Introduction to SQL Joins

July 12, 2024·Pawan Kumar

Tags: · ·

Traditionally, pulling data from two or more tables requires using a WHERE clause in a query. However, in a relational database system (RDBMS), this can be achieved with a single SELECT query, showcasing the true power of RDBMS. This guide introduces SQL Joins, a powerful method for comparing and selecting rows and tables.

What is a SQL Join?

In SQL, a join clause extends the capability of comparing and selecting rows from tables. It uses an algebraic process of combining rows from two or more tables based on a related column in those tables. By the ANSI-standard SQL definition, there are five types of Joins –Cross Joins, Inner Joins, Left (Outer) Joins, Right(Outer) Joins, and Full (Outer) Joins. These Joins are implemented across all relational database systems and are covered in the sections below.

Joins can be performed on any number of tables in a given query. For brevity and clarity, this guide discusses Joins applied to two tables.

This guide uses two tables, Employees and Address, respectively, to demonstrate SQL Joins. Each of these tables contain the following column definitions and data:

Employees Table
EmployeeId EmployeeName
1 John
2 Mary
3 Robert
Address Table
Id State
1 New York
2 New Jersey
3 Idaho
4 Hawaii

EmployeeId	EmployeeName
1	John
2	Mary
3	Robert

Id	State
1	New York
2	New Jersey
3	Idaho
4	Hawaii

Unless mentioned otherwise, all the commands in this guide work well on both **MySQL** and **PostgreSQL** databases.

SQL Cross Joins

Also known as a Cartesian Join, Cross Joins occur when you specify multiple tables as a source for your SELECT column list. In this case, you leave out the WHERE clause join expression to match rows on. The result set contains a row for every combination of rows between the tables. In a two-table scenario, every row in one table is paired with every row of the other table. The resulting product is known as the Cartesian Product of the two tables. The syntax for a Cross Join is the following:

(# Rows in Table A) TIMES (# of Rows in Table B)

In set theory, the Cartesian Product is a multiplication operation that generates all ordered pairs of the given sets. For example, consider set `A` with elements `{a,b}` and set `B` with elements `{1,2,3}`. The Cartesian Product of `A` and `B` is denoted by `AxB` and the result is the following: AxB ={(a,1), (a,2), (a,3), (b,1), (b,2), (b,3)}

The SQL syntax for a Cross Join is as follows:

SELECT ColumnName_1,
       ColumnName_2,
       ColumnName_N
FROM [Table_1]
     CROSS JOIN [Table_2]

From the above syntax, Column_1, Column_2, Column_N represent the columns in a table, and the CROSS JOIN clause serves to combine the two tables, Table_1 and Table_2. From the example tables above, if you need to perform a Cross Join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
CROSS JOIN Address

The output of the above SQL code resembles the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| John         |   New Jersey |
| John         |   Idaho      |
| John         |   Hawaii     |
| John         |   New York   |
| Mary         |   New York   |
| Mary         |   New Jersey |
| Mary         |   Idaho      |
| Mary         |   Hawaii     |
| Robert       |   New York   |
| Robert       |   New Jersey |
| Robert       |   Idaho      |
| Robert       |   Hawaii     |
+------------+----------------+

SQL Inner Join

An Inner Join returns rows that have matching values in both tables. If there are no matching records, then no rows are returned in the results.

The SQL syntax for Inner Join is as follows:

SELECT ColumnName_1,
       ColumnName_2,
       ColumnName_N
FROM Table_1
INNER JOIN Table_2
ON Table_1.key = Table_2.key;

In the example above, key is the respective key of the tables. If you need to perform an inner join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
INNER JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code resembles the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
+------------+----------------+

SQL Left (Outer) Join

A Left Join returns a complete set of rows from the left table along with the matching rows from the right table. If there are no matching records, then NULL values are returned from the right table.

Some relational database implementations use the keywords “Left Outer Join”, as opposed to “Left Join”, but they are functionally equivalent.

The SQL syntax for Left Join is as follows:

SELECT * FROM Table_1
LEFT JOIN Table_2
ON Table_1.key = Table_2.key

In the example above, key is the respective key of the tables. If you need to perform a left join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
LEFT JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code is as follows:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
| Robert       |   NULL       |
+------------+----------------+

SQL Right (Outer) Join

A Right Join returns a complete set of rows from the right table and the matching rows from the left table. This is also known as a Right Outer Join. If there are no matching records, then NULL values are returned from the right table, for the affected rows in the left table.

Some relational database implementations use the keywords "Right Outer Join”, as opposed to "Right Join”, but they are functionally equivalent.

The SQL syntax for a Right Join is as follows:

SELECT * FROM Table_1
RIGHT JOIN Table_2
ON Table_1.key = Table_2.key

From the above code, key is the respective key of the tables. If you need to perform a right join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
RIGHT JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code is the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
| NULL         |   Idaho      |
| NULL         |   Hawaii     |
+------------+----------------+

SQL Full (Outer) Join

A Full Join returns all rows from the left table, all rows from the right table. This is also known as also known as a Full Outer Join. A Full Join also returns all matching records from both tables where available. If there are no matching records, then NULL values are returned from the left table. It also returns NULL values from the right table.

Some relational database implementations use the keywords "Full Outer Join”, as opposed to "Full Join”, but they are functionally equivalent.

The SQL syntax for Full Join is as follows:

SELECT * FROM Table1
FULL JOIN Table2
ON Table1.key = Table2.key

In the above code, key is the respective key of the tables. If you need to perform a full join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
FULL JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code is the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
| Robert       |   NULL       |
| NULL         |   Idaho      |
| NULL         |   Hawaii     |
+------------+----------------+

During Join calculations, if you compare table data with `NULL` values, they do not match one another. Hence, `NULL` values are only returned as part of Join results and are ignored during Join calculations.

Performance Comparison of SQL Joins

Considering the example tables above, the Inner Join generally offers the fastest database performance among the five Join types. Left Join and Right Join follow in terms of speed, depending on the size of the two tables. Full Join is typically slower than Left Join or Right Join. Cross Join, which relies on the Cartesian product of the two tables, is usually the slowest. However, this performance hierarchy can vary based on factors such as table column length, column datatype, and key definitions.

Conclusion

SQL Joins enhance the ability to compare table rows beyond the traditional WHERE clause queries. They are a valuable mechanism for applying algebraic logic to two or more tables.

To learn more about SQL, see our guides on SQL data types, grouping and totaling, and SQL user management security.

Introduction to SQL Grouping and Totaling Part 1: SQL Server Security Best Practices