The Joins in SQL

A few main points about the Joins are:

  • The Join clause is used to combine data of two or more tables into one temporary table.
  • Joins in SQL combine data of two or more tables by the common keys.
  • The temporary table is just logical, that is each table is still physically independent with its own structure after using the SQL Join clause.

Generally, you store the data in different tables in the relational databases. These tables are then related by the foreign keys or some common columns to keep a relationship between the data. For example, in a departmental store’s point of sales system, employees data may be stored in one table called tbl_employee. This table only stores employees/operators data.

The other table, tbl_products may store the products information in the store. As consumers come to the shop and complete the orders, these order should be related to an operator/employee.

So at the end of the Day, Week or Month, you may need a report that which employee has taken how many orders? And which products were ordered in each of those orders?

As the information is stored in different tables, how you will be able to see a report in a single format?

This is where Joins play its role.

SQL join syntax

The general syntax of using the join clause in SQL is:

Select * from table_name1

Join table_name2 ON table_name1.common_field = table_name2.common_field

Examples of using the join clause

Following are the join examples. For our examples, we will use the same scenario as explained in the above section. A departmental store’s database contains these tables:

  1. tbl_employee
  2. tbl_customers_orders
  3. tbl_products

Where the tbl_employee stores the employee’s data, tbl_prodcuts stores the products in the store and tbl_customers_orders stores the orders placed by the customers. The order information may include customer id, product_id and also employee_id associated with that order.

(Note that, this is just a simplified scenario to illustrate how to use the joins.)

A join example to combine rows of two tables

In this example, we will use the join SQL clause to fetch employees and order information for those employees who have taken those orders.

The join query will be:

See the graphic of all tables and join

As you can see in the above graphic, first data of two tables are shown followed by the join query.

Example of using SQL inner join type to combine rows of two tables

In this example, we will use the inner join clause to fetch employees and order information for those employees who have taken orders. Note that, the employees records will not be retrieved whom emp_id are not matched in both tables.

The join query:

See the graphic of all tables and join

As you can see in the above graphic, first data of two tables are shown which is followed by the SQL query with the join clause.

As shown in the graphic, it only returned the matched rows. That is, only those records are retrieved for employees whom emp_ids are in the tbl_customers_orders table.

Example of using left join in SQL to combine rows of two tables

This example will retrieve all records from the left table and only matched records from the joined or right table by using the SQL left join type.

The join query will be:

See the graphic of all tables and join in SQL

As you can see in above graphic, all rows are retrieved from the tbl_emploee table, whereas, the columns for tbl_customers_orders are filled with Null values.

Example of using right join type

The example shows how to use the right join type. This example will retrieve all records from the right table and only the matched records from the left table by using the SQL right join type.

The join query:

See the graphic of all tables and join

As you can see in the above graphic, all rows are retrieved from the tbl_customers_orders by using the right join. 

Types of joins in SQL

Following are the join types supported in SQL:

  1. SQL Inner join
  2. SQL left join
  3. SQL Right join
  4. SQL Full Outer join

Each of these joins types is explained in their respective chapters with examples.