The JOINS in SQL

In relational databases, we store data in various tables. For example, in a departmental store database, the products information is stored in one table and orders information in another table.

Similarly, employees information working in that store is stored in another table. However, the information in all these tables is related to each other.

For example, an order is placed by an employee that contains product information. In RDBMS, the SQL JOIN clause is the way for combining records of different tables. The tables are joined by logical relationships.

For example, the sto_order table stores the employee _id and product_id. On the basis of those IDs, we may get the detailed information of employee and product from the sto_employee and sto_products tables, respectively. The JOIN clause enables us to do that easily.

See the examples of JOINs in SQL which is followed by its commonly used types and links to their respective tutorials.

The example of SELECT with JOIN clause

For explaining the JOIN clause, I will use a few demo tables with dummy data. The following tables will be used in this and coming examples and understand how these are related; that will help understanding JOIN and its various types.

  • sto_employees
  • sto_orders
  • sto_products

The sto_employees table stores the information of the employees working in a departmental store. The information includes employee ID, name, age, salary and joining date.

The sto_products table stores the information of the products in the store.

The third table is sto_orders that stores the orders placed by customers and stores employee ID who has taken the order, product IDs and order date.

In the first example, we will use a simple join and retrieve the complete data from sto_employees and sto_orders. Both tables are related by the employee ID.

The JOIN Query:

SQL JOIN

From the above graphic, you can see the data of two tables. The third recordset is the result of using JOIN query that retrieves the data based on matches found for employee id.

Note that, in the above query the sto_employees is the left table (The table used in SELECT statement)

The sto_orders is the right table (the table used after the JOIN clause). Remember these, as it will help in understanding the JOIN types.

SQL JOIN types

The SQL has four types of JOINs. Depending on the requirement of the recordset, you may choose which one to choose. Following are the join types with a little description which is followed by an example of each:

  • The INNER JOIN

This is the default JOIN. If you simply use JOIN keyword, it is taken as INNER JOIN. The INNER JOIN only returns the matched records from both tables.

In the above example, you might noticed that tbl_employee contains more employees data. However, it returned only those records that are matched with the emp_id in sto_orders table.

  • LEFT OUTER JOIN

This is also called LEFT JOIN. The LEFT JOIN retrieves all records from the left table and only matched records from the right table.

  • RIGHT OUTER JOIN

Also known as RIGHT JOIN, returns all records from the right table and only matched records from the left table.

  • FULL OUTER JOIN

The FULL OUTER JOIN or FULL JOIN fetches all records from both tables.

You have already seen an example of INNER JOIN. The following section shows examples of the other JOIN types.

An example of LEFT JOIN

The LEFT JOIN returns all matched or unmatched results from the left table while only matched records from the right table. The following query shows how it works:

The LEFT JOIN Query:

SQL LEFT JOIN

I used table alias in this example. You may notice the NULL values for the orders tables (right table) for the unmatched records of left table (employees).

The example of RIGHT JOIN

This query shows how RIGHT JOIN works in SQL.

The Query:

SQL RIGHT JOIN

The FULL OUTER JOIN example

Use the FULL OUTER JOIN or FULL JOIN for using this type. This should return all records from the left and right tables:

The Query:

SQL FULL OUTER JOIN

As such, the st_orders table must contain an emp_id for each record so no mismatch is found for that table.

The example of using Where clause with JOIN

After the JOIN clause, you may use the WHERE clause for filtering the results based on certain criteria. For specifying a column name in the WHERE clause, use the table name or alias for referring a column if that column exists in both tables. Otherwise, you may use the column without table name prefix.

See this query of JOIN with WHERE clause:

SQL JOIN WHERE

The Example of using GROUP BY clause with JOIN

The following examples uses an INNER JOIN on two tables just like the above examples and groups the result based on employee names after getting the SUM of salaries.

Have a look at this combination with GROUP BY clause:

The query:

The result:

SQL JOIN GROUP BY

Adding HAVING clause in the above example

Just like simply using GROUP BY clause with HAVING, you may use it with the JOIN clause as well. For demonstrating that, I just added the HAVING clause in the above query and specified an expression to return those records with the sum of salary greater than 3000.

The query:

The result:

SQL JOIN HAVING

Using ORDER BY clause with JOIN example

For sorting the results in asending or descending order, you may use the ORDER BY clause in a query with JOIN clause. You may use it with GROUP BY clause (at the end even after HAVING clause) or in a simple JOIN.

With  GROUP BY clause:

In simple query:

The result of this query in our example tables:

SQL JOIN ORDER BY