Description

In the earlier chapters, we have looked at the SQL queries that involve one table. But, those will not be sufficient while building a complex application.

We often need to execute SQL queries by combining two or more tables, which is technically referred to as a join.

  • A SELECT statement to fetch data from two or more tables.
  • An UPDATE statement to update a column on one table by comparing something on another table.
  • A DELETE statement to delete a record when it exists on another table.

In reality, RDBMS database systems contain multiple tables in a database, which have relationships with each other using foreign keys upon common fields.

In this chapter, let's take a closer look at the types of SQL Joins.

Relationships Between Tables

Let's consider the below data in the orders, customers, and shippers tables, where we can observe the below relationships.

  • Column customer_id on the table orders is relationally connected to the column customer_id on the table customers.
  • Column customer_id on the table orders is relationally connected to the column customer_id on the table shippers.

These relationships between the tables will help us in writing join queries.

In order to join tables, the data on the columns that we use for joining tables must match, not necessarily the column names.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 2 3
10251 2014-07-15 1159 1 2

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199

Types of Joins

When we join two or more tables in a query, the type of join determines the rows in its result set.

  • When we call a join, we simply mean an inner join.
Type of Join Description
INNER JOIN It returns all the rows that have a match between the joined tables.
LEFT JOIN It returns all rows from the left table along with the data from the right table for the matching records.
In case, the matching rows are not found on the right table, a NULL value is used for the columns coming from that table.
RIGHT JOIN It returns all rows from the right table along with the data from the left table for the matching records.
In case, the matching rows are not found on the left table, a NULL value is used for the columns coming from that table.
FULL JOIN It returns all the rows from both the tables along with the data from the other table for the matching records.
It combines the functionality of the LEFT JOIN and RIGHT JOIN, so as the result set.
CROSS JOIN It returns a Cartesian product or multiplication of both the joined tables, where each row of one table is combined with each row of another table.
So, it returns each possible combination of the data from the two tables.
Self Join It is a regular join without a JOIN clause, where the table is joined with itself.

Overall

We now understood what SQL Joins are and their types.

Related Links