Using SQL JOIN Clause
Introduce to SQL JOIN Clause
In real world programming contexts, you usually need to select data from two or more than two tables to make your result complete. in such cases, you need SQL JOIN.
Because of normalnization process so the big tables are divided into a number of smaller tables and related toghether by foreign keys. for each example, here are the 3 tables related toghether.
Employees table
employee_id name department_id job_id salary
----------- -------- ------------- ------ -------
1 jack 1 1 3000.00
2 mary 2 2 2500.00
3 newcomer (NULL) 0 2000.00
4 anna 1 1 2800.00
5 Tom 2 2 2700.00
6 foo 3 3 4700.00
department_id name
------------- --------------------
1 Software Engineering
2 Sale
3 Marketing
4 HR
job_id description
------ ---------------
1 Software Engine
2 Markette
3 Manager
We have departments, employees and jobs tables. In order to get sufficient data of an employee you need refers two three tables like this: (If you don't have SQL sample data to practice, you can get it here in SQL sample data section)
SELECT employee_id,
E.name AS name,
salary,
D.name AS department_name,
jobs.description AS job_description
FROM employees AS E,
departments AS D,
jobs
WHERE E.department_id = D.department_id
AND E.job_id = jobs.job_id
Using INNER JOIN
INNER JOIN return the result from all tables listed after it based on condition after keyword ON. In case, the conditions are not sastified, it returns nothing. Here is a version of SQL INNER JOIN as the query above.
SELECT employee_id,
E.name AS name,
salary,
D.name AS department_name,
jobs.description AS job_description
FROM employees AS E
INNER JOIN departments AS D
ON E.department_id = D.department_id
INNER JOIN jobs N E.job_id = jobs.job_id
In case, you want to get the result from on table and doesn't care about whether other table can match condition or not, you can use SQL LEFT JOIN or SQL RIGHT JOIN
Using SQL LEFT JOIN and SQL RIGHT JOIN
In our data sample, if a new comer go to the company and he doesn't belong to any department but the HR department need the employee list, you can use LEFT JOIN like this
SELECT employee_id,
employees.name AS name,
salary,
departments.name AS department_name
FROM employees AS E
LEFT JOIN departments AS D
ON E.department_id = D.department_id
Here is the complete employee list
employee_id name salary department_name
----------- -------- ------- --------------------
1 jack 3000.00 Software Engineering
2 mary 2500.00 Sale
3 newcomer 2000.00 (NULL)
4 anna 2800.00 Software Engineering
5 Tom 2700.00 Sale
6 foo 4700.00 Marketing
As you see, the SQL LEFT JOIN allows you select data from multiple table and returns all rows from the first table (employees table) even there no matches in other tables(departments table).
The general syntax of SQL LEFT JOIN
SELECT field_list
FROM first_table
LEFT JOIN second_table
ON first_table.key = second_table.foreign_key
LEFT JOIN third_table
ON first_table.key = third_.foreign_key
...
SQL RIGHT JOIN is similar to SQL LEFT JOIN. It returns all rows from second table, third table... but first table in SQL command text. Change the SQL LEFT JOIN above to RIGHT JOIN as follows to see the difference
SELECT employee_id,
E.name AS name,
salary,
D.name AS department_name
FROM employees AS E
RIGHT JOIN departments
ON E.department_id = D.department_id
Here all departments are listed and the data from employees table is missing
employee_id name salary department_name
----------- ------ ------- --------------------
1 jack 3000.00 Software Engineering
4 anna 2800.00 Software Engineering
2 mary 2500.00 Sale
5 Tom 2700.00 Sale
6 foo 4700.00 Marketing
(NULL) (NULL) (NULL) HR
Read On