JOINS in SQL

An infographic showing various types of JOINs in SQL. The queries are shown for INNER JOIN, LEFT JOIN, RIGHT JOIN, and OUTER JOIN. Enhance your relational database knowledge with this visual tutorial.

Why use JOINs in SQL?

In relational databases, we store data in various tables. For example, in a departmental store database, the product information is stored in one table, and order 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 to combine records from different tables. The tables are joined by logical relationships.
--Simple JOIN
SELECT * FROM sto_employees
JOIN sto_orders  ON sto_employees.id=sto_orders.emp_id

--LEFT JOIN
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

 

Similarly, the sto_order table stores the employee _id and product_id. On the basis of those IDs, we may get detailed information about employees and products 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

To explain the JOIN clause, I will use a few demo tables with dummy data. The following tables will be used in this and the coming examples and understanding how these are related will help in 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 which 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:

SELECT * FROM sto_employees

JOIN sto_orders  ON sto_employees.id=sto_orders.emp_id

SQL JOIN

From the above graphic, you can see the data of two tables. The third recordset is the result of using the 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 the 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 the 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 notice that tbl_employee contains more employee data. However, it returned only those records that are matched with the emp_id in the sto_orders table.

LEFT OUTER JOIN

This is also called LEFT JOIN.

The LEFT JOIN retrieves all records from the left table and only matches records from the right table.

RIGHT OUTER JOIN

Also known as RIGHT JOIN, returns all records from the right table and only matches 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 LEFT JOIN Query:

SELECT * FROM sto_employees SE

LEFT JOIN sto_orders SO  ON SE.id=SO.emp_id

SQL LEFT JOIN

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

The example of RIGHT JOIN

This query shows how RIGHT JOIN works in SQL.

The Query:

SELECT * FROM sto_employees SE

RIGHT JOIN sto_orders SO  ON SE.id=SO.emp_id

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:

SELECT * FROM sto_employees SE

FULL OUTER JOIN sto_orders SO  ON SE.id=SO.emp_id

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 to filter the results based on certain criteria.

For specifying a column name in the WHERE clause, use the table name or alias for referring to a column if that column exists in both tables. Otherwise, you may use the column without the table name prefix.

See this query of JOIN with WHERE clause:

SELECT * FROM sto_employees SE

JOIN sto_orders SO  ON SE.id=SO.emp_id

WHERE emp_salary>=3500;

SQL JOIN WHERE

The Example of using GROUP BY clause with JOIN

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

Have a look at this combination with the GROUP BY clause:

The query:

SELECT SUM(emp_salary),emp_name FROM sto_employees SE

JOIN sto_orders SO  ON SE.id=SO.emp_id

GROUP BY emp_name;

The result:

SQL JOIN GROUP BY

Adding the HAVING clause in the above example

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

The query:

SELECT SUM(emp_salary) ,emp_name FROM sto_employees SE

JOIN sto_orders SO  ON SE.id=SO.emp_id

GROUP BY emp_name

HAVING SUM(emp_salary) >3000;

The result:

SQL JOIN HAVING

Using ORDER BY clause with JOIN Example

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

With  GROUP BY clause:

SELECT SUM(emp_salary) ,emp_name FROM sto_employees SE

JOIN sto_orders SO  ON SE.id=SO.emp_id

GROUP BY emp_name

HAVING SUM(emp_salary) >3000

ORDER BY emp_name;

In simple query:

SELECT * FROM sto_employees

JOIN sto_orders  ON sto_employees.id=sto_orders.emp_id

ORDER BY  emp_name DESC;

The result of this query in our example tables:

SQL JOIN ORDER BY

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we unravel the mysteries of coding together!