Featured

Joins in SQL

Often, data that you need to query is spread across multiple tables. The more normalized the environment is, the more tables you usually have. The tables are usually related through keys, such as foreign key in one side and primary key in the other. Then you can use joins to query the data from different tables and match the rows that need to be related.

There are three types of joins in SQL

  1. Cross Join
  2. Inner Join
  3. Outer Join

Cross Join

A cross join is the simplest type of join, though not the most commonly used one. This join performs what’s known as Cartesian product of the two input tables. In other words, it performs a multiplication between the tables, yielding a row from each combination of rows from each sides. If you have m rows in table T1 and n rows in table T2, the result of a cross join between T1 and T2 is a virtual table with m x n rows.

Cross Join

The left table has three rows with the key values A, B, and C. The right table has four rows with key values B1, C1, C2, and D1. The result is a table with 12 rows containing all possible combinations of rows from the two input tables.

The syntax of cross join is as follow:

SELECT *  FROM table1
CROSS JOIN table2;

Inner Join

With an inner join, you can match rows from two tables based on a predicate- usually one that compares a primary key value in one side to a foreign key value in another side.

Inner join.png

The letters represent primary key values in the left table and the foreign key values in the right table. The inner join returns only matching rows for which the predicate evaluates to true. Rows for which the predicate evaluates false or unknown are discarded.

The syntax for inner join is as follows:

SELECT *
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
OR

SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Outer Join

Outer join can be further classified into three forms

  • Left Outer Join / Left Join
  • Right Outer Join / Right Join
  • Full Outer Join / Full Join

With outer joins, you can request to preserve all rows from one or both sides of the join.

LEFT OUTER JOIN (or LEFT JOIN for short):  You ask to preserve the left table. The join returns what an inner join normally would—that is, matches (call those inner rows). In addition, the join also returns rows from the left that had no matches in the right table (call those outer rows), with NULLs used as placeholders in the right side.

For simplicity, you can say that

Left Outer Join =  Inner Join + Records from left table that don’t have any match

Left outer join.png

Unlike in the inner join, the left row with the key A is returned even though it had no match in the right side. It’s returned with NULLs as placeholders in the right side. The syntax for left outer join is as follow:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Right Outer Join / Right Join: You ask to preserve the right table. The join returns what an inner join normally would—that is, matches (call those inner rows). In addition, the join also returns rows from the right that had no matches in the left table (call those outer rows), with NULLs used as placeholders in the right side.

For simplicity, you can say that

Right Outer Join =  Inner join + Records from right table that don’t have any match

Right Outer Join.png

Unlike in the inner join, the right row with the key D1 is returned even though it had no match in the left side. It’s returned with NULLs as placeholders in the right side. The syntax for right outer join is as follow:

SELECT column_name(s)
FROM table1
Right JOIN table2 ON table1.column_name = table2.column_name;

Full Outer Join: T-SQL also supports a full outer join (FULL OUTER JOIN, or FULL JOIN in short), that preserves both sides. For simplicity, you can say that full outer join is

Full Outer Join =  Inner join + Records from right table that don’t have any match + Records from left table that don’t have any match

Full outer join.png

A full outer join returns the inner rows that are normally returned from an inner join; plus rows from the left that don’t have matches in the right, with NULLs used as placeholders in the right side; plus rows from the right that don’t have matches in the left, with NULLs used as placeholders in the left side.

Advertisements