SQL: Joins


Simple queries are useful when we want to explore data or just to have a look at the database structure. But the power of SQL starts when we create complex queries by mixing records from several tables. For instance:

List each order placed last year, showing the order amount, the name of the customer who placed it, and the name of the product ordered (Orders, Customers, and OrderDetails tables)

The process of combining records by matching the contents of related columns (common values) is called joining the tables.
The resulting table (containing data from both of the original tables) is called a join between the two tables.
But, there are many ways to join tables.
SQL_Joins

In this article, we will see:

  1. INNER JOIN
  2. OUTER JOINS
    1. LEFT (OUTER) JOIN
    2. RIGHT (OUTER) JOIN
    3. (FULL) OUTER JOIN
  3. CROSS JOINS
  4. SELF REFERENCING JOINS
  5. ADDITIONAL NOTES RELATED TO JOINS
    1. LEFT JOIN EXCLUDING INNER JOIN
    2. RIGHT JOIN EXCLUDING INNER JOIN
    3. OUTER JOIN EXCLUDING INNER JOIN

In order to have a better understanding, we are going to work with the following example:

Relational databases are usually normalized to eliminate duplication of information such as when objects have one-to-many relationships. For example, in a school, a child is associated with his/her guardian. Running the SQL statements above, we should have the following tables:

Screen Shot 2015-02-24 at 15.59.35

Inner Joins

An INNER JOIN will return all the records in the left table (table Children) that have a matching record in the right table (table Guardian)

INNER_JOIN

Picture Reference: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

SQL specifies two different syntactical ways to express joins: “explicit join notation” and “implicit join notation”. For instance:

Tip: Although “implicit join notation” was deprecated in 1992, and its use is not considered a best practice, database systems still support it.

Using the explicit join notation:

 

Screen Shot 2015-02-24 at 16.43.37
As we can see in the query results, Elton (child) and Heisenberg (guardian) were not listed on the results.

OUTER JOINS

LEFT JOIN

This query will return ALL the records in the left table (table Children) and the matched rows from the right table (Table Guardian)

LEFT_JOIN

 

Screen Shot 2015-02-24 at 16.59.34

We can see that Elton (child, left table) is now listed but Heisenberg (on the right table) is not.

RIGHT JOIN

Return all rows from the right table, and the matched rows from the left table.

RIGHT_JOIN

Screen Shot 2015-02-24 at 17.01.42

We see that Heisenberg (guardian, right table) is listed on the query results, but Elton is out.

(FULL) OUTER JOIN

Conceptually, a full outer join combines the effect of applying both left and right outer joins.

FULL_OUTER_JOIN

A.k.a. FULL OUTER JOIN or FULL JOIN, this query will return ALL the records from both tables, joining records from the left table that match records from the right table.

Some database systems do not support the full outer join functionality directly, but they can emulate it through the use of an inner join and UNION.
For instance, in MySQL, we should query:

Screen Shot 2015-02-24 at 17.28.02

On the query results we can see that both Elton and Heisenberg are listed.

CROSS JOINS

Cross join computes the Cartesian product of rows. Each row from the first table is combined with each row from the second table.

cross join - half

Example of an explicit cross join:

Example of an implicit cross join:

SELF REFERENCING JOINS

A self-join is joining a table to itself.

I think the “self join” is not a join, but a concept. We will see that we can use Left Join or Right Join to accomplish this kind of query. The concept of a “self join” allow us to explain the fact that we can join a table to itself, and will need to if the data in the table is hierarchical, for instance Employee – Manager, Child – Parent, etc.

Let’s come back to the Classic Models Sample database and check the table Employees.

Screen Shot 2015-02-24 at 23.04.47

We can see the “self” relationship through the Primary key EmployeeNumber and the field reportsTo.
We can explore the table:

Screen Shot 2015-02-24 at 23.07.48

Now, let’s try the following query:

List the employees with the names of their managers (bosses).

We implement this Self Join as an Inner Join as well as an Outer Join.

1) As an Outer Join

Screen Shot 2015-02-24 at 23.25.51

The query result is a list of all the employees with their respective bosses. The employee number 1002 is supposed to be the manager, and he/she doesn’t have a boss.

2) As an inner join

Screen Shot 2015-02-24 at 23.28.57
We see the same list as before. However we are unable to find out the manager (Employee 1002) as he/she is not visible in our result set. The inner join has filtered the manager out.

ADDITIONAL NOTES RELATED TO JOINS

LEFT JOIN EXCLUDING INNER JOIN

This query will return all the records in the left table (A) that do not match any records in the right table (B)

LEFT_EXCLUDING_JOIN

In our example about children and guardians:

List the children who don’t have a guardian.

Screen Shot 2015-02-24 at 23.34.34
The only field (left table) that doesn’t match with any record (right table) is “Elton”

RIGHT JOIN EXCLUDING INNER JOIN

This query will return all the records in the right table (B) that do not match any records in the left table (A).

RIGHT_EXCLUDING_JOIN

 

List the guardians who don’t have a child assigned.

 

Screen Shot 2015-02-24 at 23.36.50

The only record (table B) that doesn’t have a match (table A) is “Heisenberg”.

OUTER JOIN EXCLUDING INNER JOIN

This query will return all the records in the left table (A) and all the records in the right table (B) that do not match.

OUTER_EXCLUDING_JOIN

For those database systems that support the full outer join functionality directly:

In MySQL we need to emulate it. For instance:

List the children who don’t have a guardian AND the guardians who don’t have a child assigned.

Screen Shot 2015-02-24 at 23.41.47

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS