Beginning Joins with SQL

Written by Jeff B411 on Posted in Database

Introduction

When we are talking about relational database, we are usually talking about many different tables and the relationships between them. We define relationships with various keys like primary key, foreign key, etc. When we have to retrieve data from more than one table, we have to use Joins. In this article we will be discussing how we can code joins effectively and retrieve data from more than one table.

Try Rapid SQLThis article applies to various relational databases like SQL Server, MySQL, PostgreSQL, Oracle and others.

The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.

In this blog post we discuss about following joins.

  • Inner Join
  • Outer Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join

Inner JOIN

A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN. This is the default type of JOIN in the Query and View Designer.

Inner Join

Outer JOIN

A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN. You can create three different outer JOINs to specify the unmatched rows to be included:

Left Outer JOIN

In Left Outer JOIN, all rows in the first-named table, i.e., the “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.

Left Outer Join

Right Outer JOIN

In Right Outer JOIN, all rows in the second-named table, i.e., the “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.

Right Outer Join

Full Outer JOIN

In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.

Full Outer Join

Download eBook "SQL Server in Simple Words" by Pinal DaveConclusion

With the help of Venn diagrams we can more easily understand how joins work. Besides Inner Join and Outer Join, other kinds of joins are possible (cross join, natural joins, etc.). However, the most frequently used joins are inner and outer join which are explained in this article.

Next Steps

For more SQL tips, download our ebook, SQL Server in Simple Words, by Pinal Dave. Learn more about Embarcadero Rapid SQL, the intelligent IDE for SQL development, and try Rapid SQL for free.



About
Gold User, Rank: 92, Points: 2

Check out more tips and tricks in this development video: