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.
--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
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.
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.
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.
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
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
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
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;
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:
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:
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:
