In Standard SQL, the JOIN() uses at least two tables. It’s time to learn how to create temporary table with WITH() clause.
A WITH clause contains one or more common table expressions (CTEs). A CTE acts like a temporary table that you can reference within a single query expression. Each CTE binds the results of a subquery to a table name, which can be used elsewhere in the same query expression.
Let’s create a table called Human_Resource by WITH clause and then use SELECT statement to retrieve data.
WITH Human_Resource AS (
SELECT 1001 AS E_id, "Tom" AS E_Name, "IT" AS department
UNION ALL
SELECT 1002 AS E_id, "Jane" AS E_Name, "Marketing" AS department
UNION ALL
SELECT 1003 AS E_id, "Luca" AS E_Name, "Engineering" AS department
UNION ALL
SELECT 1004 AS E_id, "Mary" AS E_Name, "Finance" AS department
UNION ALL
SELECT 1005 AS E_id, "Ivy" AS E_Name, "Data Science" AS department)
SELECT * FROM Human_Resource;
WITH Finance AS (
SELECT 1001 AS E_id, 70000 AS salary
UNION ALL
SELECT 1002 AS E_id, 65000 AS salary
UNION ALL
SELECT 1003 AS E_id, 68000 AS salary
UNION ALL
SELECT 1004 AS E_id, 66000 AS salary
UNION ALL
SELECT 1006 AS E_id, 72000 AS salary
UNION ALL
SELECT 1007 AS E_id, 75000 AS salary)
SELECT * FROM Finance;Different JOIN Types and Examples
Inner JOIN
It returns only the rows that have matching values in both tables.
SELECT HR.E_id, HR.E_Name, HR.department, F.salary
FROM Human_Resource HR
INNER JOIN Finance F ON HR.E_id = F.E_id;LEFT JOIN
It returns all rows from the left table (Human_Resource), and the matched rows from the right table (Finance). If there is no match, the result is NULL on the right side.
SELECT HR.E_id, HR.E_Name, HR.department, F.salary
FROM Human_Resource HR
LEFT JOIN Finance F ON HR.E_id = F.E_id;RIGHT JOIN
It returns all rows from the right table (Finance), and the matched rows from the left table (Human_Resource). If there is no match, the result is NULL on the left side.
SELECT HR.E_id, HR.E_Name, HR.department, F.salary
FROM Human_Resource HR
RIGHT JOIN Finance F ON HR.E_id = F.E_id;OUTER JOIN
It returns all rows when there is a match in either left or right table. If there is no match, the result is NULL on the opposite side.
SELECT HR.E_id, HR.E_Name, HR.department, F.salary
FROM Human_Resource HR
FULL OUTER JOIN Finance F ON HR.E_id = F.E_id;CROSS JOIN
It returns the Cartesian product of the two tables, i.e., each row from the first table is combined with each row from the second table.
SELECT HR.E_id, HR.E_Name, HR.department, F.salary
FROM Human_Resource HR
CROSS JOIN Finance F;How to understand Cartesian product?
