Join

Join the fun! Use the SMA today!

Description

Combines the rows from two table references based on join criteria. (Databricks SQL Language Reference JOIN)

A JOIN operation combines rows from two tables (or other table-like sources, such as views or table functions) to create a new combined row that can be used in the query. For a conceptual explanation of joins, see Working with Joins. (Snowflake SQL Language Reference JOIN)

Syntax

Databricks

left_table_reference { [ join_type ] JOIN right_table_reference join_criteria |
           NATURAL join_type JOIN right_table_reference |
           CROSS JOIN right_table_reference }

join_type
  { [ INNER ] |
    LEFT [ OUTER ] |
    [ LEFT ] SEMI |
    RIGHT [ OUTER ] |
    FULL [ OUTER ] |
    [ LEFT ] ANTI |
    CROSS }

join_criteria
  { ON boolean_expression |
    USING ( column_name [, ...] ) }

Snowflake

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Sample Source Patterns

Setup data

Databricks

Query

-- Use employee and department tables to demonstrate different type of joins.
CREATE TEMP VIEW employee(id, name, deptno) AS
     VALUES(105, 'Chloe', 5),
           (103, 'Paul', 3),
           (101, 'John', 1),
           (102, 'Lisa', 2),
           (104, 'Evan', 4),
           (106, 'Amy', 6);

CREATE TEMP VIEW department(deptno, deptname) AS
    VALUES(3, 'Engineering'),
          (2, 'Sales'      ),
          (1, 'Marketing'  );

Snowflake

Query

-- Use employee and department tables to demonstrate different type of joins.
CREATE TEMPORARY TABLE employee(id, name, deptno) AS
SELECT id, name, deptno
  FROM (VALUES (105, 'Chloe', 5),
           (103, 'Paul' , 3),
           (101, 'John' , 1),
           (102, 'Lisa' , 2),
           (104, 'Evan' , 4),
           (106, 'Amy'  , 6)) AS v1 (id, name, deptno);

CREATE TEMP VIEW department(deptno, deptname) AS
SELECT deptno, deptname
  FROM (VALUES(3, 'Engineering'),
          (2, 'Sales'      ),
          (1, 'Marketing'  )) AS v1 (deptno, deptname);

Pattern code

Databricks

Query

-- 1. Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   INNER JOIN department ON employee.deptno = department.deptno;
 
-- 2. Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   LEFT JOIN department ON employee.deptno = department.deptno;


-- 3. Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    RIGHT JOIN department ON employee.deptno = department.deptno;


-- 4. Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    FULL JOIN department ON employee.deptno = department.deptno;


-- 5. Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    CROSS JOIN department;

-- 6. Use employee and department tables to demonstrate semi join.
SELECT *
    FROM employee
    SEMI JOIN department ON employee.deptno = department.deptno;

Results

1. Use employee and department tables to demonstrate inner join.

id	name	deptno	deptname
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales

---

1. Use employee and department tables to demonstrate left join.

id	name	deptno	deptname
105	Chloe	5	null
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales
104	Evan	4	null
106	Amy	6	null

---

1. Use employee and department tables to demonstrate right join.

id	name	deptno	deptname
103	Paul	3	Engineering
102	Lisa	2	Sales
101	John	1	Marketing

---

1. Use employee and department tables to demonstrate full join.

id	name	deptno	deptname
101	John	1	Marketing
102	Lisa	2	Sales
103	Paul	3	Engineering
104	Evan	4	null
105	Chloe	5	null
106	Amy	6	null

---

1. Use employee and department tables to demonstrate cross join.

id	name	deptno	deptname
105	Chloe	5	Engineering
105	Chloe	5	Sales
105	Chloe	5	Marketing
103	Paul	3	Engineering
103	Paul	3	Sales
103	Paul	3	Marketing
101	John	1	Engineering
101	John	1	Sales
101	John	1	Marketing
102	Lisa	2	Engineering
102	Lisa	2	Sales
102	Lisa	2	Marketing
104	Evan	4	Engineering
104	Evan	4	Sales
104	Evan	4	Marketing
106	Amy	6	Engineering
106	Amy	6	Sales
106	Amy	6	Marketing

---

1. Use employee and department tables to demonstrate semi join.

id	name	deptno
103	Paul	3
101	John	1
102	Lisa	2

Snowflake

Query

-- 1. Use employee and department tables to demonstrate inner join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   INNER JOIN department ON employee.deptno = department.deptno;
 
-- 2. Use employee and department tables to demonstrate left join.
SELECT id, name, employee.deptno, deptname
   FROM employee
   LEFT JOIN department ON employee.deptno = department.deptno;


-- 3. Use employee and department tables to demonstrate right join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    RIGHT JOIN department ON employee.deptno = department.deptno;


-- 4. Use employee and department tables to demonstrate full join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    FULL JOIN department ON employee.deptno = department.deptno;


-- 5. Use employee and department tables to demonstrate cross join.
SELECT id, name, employee.deptno, deptname
    FROM employee
    CROSS JOIN department;

-- 6. Use employee and department tables to demonstrate semi join.
SELECT e.*
    FROM employee e, department d
    WHERE e.deptno = d.deptno;

Results

1. Use employee and department tables to demonstrate inner join.

id	name	deptno	deptname
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales

---

1. Use employee and department tables to demonstrate left join.

id	name	deptno	deptname
105	Chloe	5	null
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales
104	Evan	4	null
106	Amy	6	null

---

1. Use employee and department tables to demonstrate right join.

id	name	deptno	deptname
103	Paul	3	Engineering
102	Lisa	2	Sales
101	John	1	Marketing

---

1. Use employee and department tables to demonstrate full join.

id	name	deptno	deptname
105	Chloe	5	null
103	Paul	3	Engineering
101	John	1	Marketing
102	Lisa	2	Sales
104	Evan	4	null
106	Amy	6	null

---

1. Use employee and department tables to demonstrate cross join.

id	name	deptno	deptname
105	Chloe	5	Engineering
105	Chloe	5	Sales
105	Chloe	5	Marketing
103	Paul	3	Engineering
103	Paul	3	Sales
103	Paul	3	Marketing
101	John	1	Engineering
101	John	1	Sales
101	John	1	Marketing
102	Lisa	2	Engineering
102	Lisa	2	Sales
102	Lisa	2	Marketing
104	Evan	4	Engineering
104	Evan	4	Sales
104	Evan	4	Marketing
106	Amy	6	Engineering
106	Amy	6	Sales
106	Amy	6	Marketing

---

1. Use employee and department tables to demonstrate semi join.

id	name	deptno
103	Paul	3
101	John	1
102	Lisa	2

Known Issues

No issues were found

Related EWIs

No related EWIs