If you're working with relational databases, you'll often need to combine data from multiple tables. SQL JOINs help you achieve this.
Understanding INNER JOIN
An INNER JOIN retrieves rows where there is a match between the tables based on the specified join condition. If no match is found, no rows are returned for that combination.
The syntax for an INNER JOIN looks like this:
-- [Mandatory] SELECT: Specifies the columns you want to retrieve.
SELECT
-- [Optional] DISTINCT: Ensures the result set contains only unique rows by removing duplicates.
-- Place DISTINCT immediately after SELECT if you want only unique rows.
-- DISTINCT
column1, -- Specify the first column you want to retrieve
column2, -- Specify the second column, and so on...
...
-- [Mandatory] FROM: The table from which you're selecting data.
FROM
table1
-- [Mandatory] INNER JOIN: Joins two tables and retrieves rows that have matching values in both tables.
INNER JOIN
table2 -- The second table you're joining with the first table
-- [Mandatory] ON: Specifies the condition that defines how the tables are related.
-- Only rows where the columns match will be included in the result set.
ON
table1.column = table2.column
-- [Optional] WHERE: Filters the rows returned by the join based on a specific condition.
-- Only rows that meet this condition will be included in the final result.
-- WHERE condition
-- [Optional] ORDER BY: Sorts the result set by the specified columns.
-- You can choose to sort in ascending (ASC) or descending (DESC) order.
-- ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
Understanding OUTER JOINs
While INNER JOINs return only matching rows between two tables, OUTER JOINs go further by also including rows that do not have a match in one of the tables. There are three types of OUTER JOINs:
-
LEFT OUTER JOIN (or simply
LEFT JOIN): returns all rows from the left table and the matching rows from the right table. If no match is found, the result will containNULLfor the columns from the right table. -
RIGHT OUTER JOIN (or simply
RIGHT JOIN): works in the opposite way of aLEFT JOIN. It returns all rows from the right table and the matching rows from the left table. If no match is found, the result will containNULLfor the columns from the left table. -
FULL OUTER JOIN: returns all rows when there is a match in either the left or right table. If there is no match, the result will contain
NULLfor the columns from the table without a match.
Each of these joins is useful depending on the specific data retrieval needs.
The syntax for an OUTER JOIN looks like this:
-- [Mandatory] SELECT: Specifies the columns you want to retrieve.
SELECT
-- [Optional] DISTINCT: Ensures the result set contains only unique rows by removing duplicates.
-- Place DISTINCT immediately after SELECT if you want only unique rows.
-- DISTINCT
column1, -- Specify the first column you want to retrieve
column2, -- Specify the second column, and so on...
...
-- [Mandatory] FROM: The table from which you're selecting data.
FROM
table1
-- [Mandatory] OUTER JOIN: Includes rows from the specified table even if there is no match in the other table.
-- You can use LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN based on your needs.
-- LEFT OUTER JOIN: Includes all rows from the left table and matched rows from the right table.
-- RIGHT OUTER JOIN: Includes all rows from the right table and matched rows from the left table.
-- FULL OUTER JOIN: Includes all rows from both tables, with NULLs where there is no match.
-- Example: LEFT OUTER JOIN
LEFT OUTER JOIN
table2 -- The table you're joining with the first table
-- [Mandatory] ON: Specifies the condition that defines how the tables are related.
-- Only rows where the columns match will be included in the result set, or all rows from one table depending on the join type.
ON
table1.column = table2.column
-- [Optional] WHERE: Filters the rows returned by the join based on a specific condition.
-- Only rows that meet this condition will be included in the final result.
-- WHERE condition
-- [Optional] ORDER BY: Sorts the result set by the specified columns.
-- You can choose to sort in ascending (ASC) or descending (DESC) order.
-- ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];
Example of INNER JOIN
To retrieve employee names and their associated department names, where there is a matching department for each employee, you can use an INNER JOIN:
SELECT
employees.employee_name,
departments.department_name
FROM
employees
INNER JOIN
departments
ON
employees.department_id = departments.department_id;
This query will return only the rows where there is a match between the department_id columns in both the employees and departments tables. If an employee does not have a corresponding department or a department has no employees, those rows will not appear in the result set.
Example of LEFT OUTER JOIN
Let’s say we have an employees table and a departments table, and we want to retrieve all employees, even if they are not assigned to a department:
SELECT
employees.employee_name,
departments.department_name
FROM
employees
LEFT OUTER JOIN
departments
ON
employees.department_id = departments.department_id;
In this example, the query will return all rows from the employees table, and if an employee does not belong to a department, the department_name will show as NULL.
Example of RIGHT OUTER JOIN
Let’s modify our previous example to retrieve all departments, even if they don’t have any employees assigned:
SELECT
employees.employee_name,
departments.department_name
FROM
employees
RIGHT OUTER JOIN
departments
ON
employees.department_id = departments.department_id;
This query will return all rows from the departments table, and if a department doesn’t have any employees, the employee_name will show as NULL.
Example of FULL OUTER JOIN
To retrieve all employees and all departments, including those that don’t have a match in the other table, you can use a FULL OUTER JOIN:
SELECT
employees.employee_name,
departments.department_name
FROM
employees
FULL OUTER JOIN
departments
ON
employees.department_id = departments.department_id;
This query will return all rows from both tables. If an employee is not assigned to a department, department_name will be NULL. If a department has no employees, employee_name will be NULL.
Example: LEFT OUTER JOIN with WHERE and ORDER BY
Here’s a query that retrieves all employees (even those without a department) and filters the results to only include employees from specific departments. The result is sorted in ascending order by employee name.
SELECT
DISTINCT employees.employee_name,
departments.department_name
FROM
employees
LEFT OUTER JOIN
departments
ON
employees.department_id = departments.department_id
WHERE
departments.department_name IN ('Sales', 'HR')
ORDER BY
employees.employee_name ASC;
Happy querying!
Top comments (0)