FROM clause
Last updated
Last updated
The
FROM
clause in a query lists the table references (tables, views, and subqueries) that data is selected from. If multiple table references are listed, the tables must be joined, using appropriate syntax in either theFROM
clause or theWHERE
clause. If no join criteria are specified, the system processes the query as a cross-join. ()
The is partially supported in Snowflake. is not currently supported.
FROM table_reference [, ...]
<table_reference> ::=
with_subquery_table_name [ table_alias ]
table_name [ * ] [ table_alias ]
( subquery ) [ table_alias ]
table_reference [ NATURAL ] join_type table_reference
[ ON join_condition | USING ( join_column [, ...] ) ]
table_reference PIVOT (
aggregate(expr) [ [ AS ] aggregate_alias ]
FOR column_name IN ( expression [ AS ] in_alias [, ...] )
) [ table_alias ]
table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] (
value_column_name
FOR name_column_name IN ( column_reference [ [ AS ]
in_alias ] [, ...] )
) [ table_alias ]
UNPIVOT expression AS value_alias [ AT attribute_alias ]
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
INSERT INTO employee(id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'SofÃa', 102),
(205, 'Zhang', 104);
CREATE TABLE department (
id INT,
name VARCHAR(50),
manager_id INT
);
INSERT INTO department(id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);
SELECT e.name AS employee_name, d.name AS department_name
FROM employee e
INNER JOIN department d ON e.manager_id = d.manager_id;
SELECT e.name AS employee_name, d.name AS department_name
FROM employee e
LEFT JOIN department d ON e.manager_id = d.manager_id;
SELECT d.name AS department_name, e.name AS manager_name
FROM department d
RIGHT JOIN employee e ON d.manager_id = e.id;
SELECT e.name AS employee_name, d.name AS department_name
FROM employee e
FULL JOIN department d ON e.manager_id = d.manager_id;
John
HR
Jorge
Sales
Kwaku
Sales
Liu
Sales
Mateo
Engineering
Nikki
Marketing
Paulo
Marketing
Richard
Marketing
SofÃa
Engineering
Carlos
null
John
HR
Jorge
Sales
Kwaku
Sales
Liu
Sales
Mateo
Engineering
Nikki
Marketing
Paulo
Marketing
Richard
Marketing
Saanvi
null
Shirley
null
SofÃa
Engineering
Zhang
null
HR
Carlos
Sales
John
Engineering
Jorge
Marketing
Kwaku
null
Liu
null
Mateo
null
Nikki
null
Paulo
null
Richard
null
Saanvi
null
Shirley
null
SofÃa
null
Zhang
Carlos
null
John
HR
Jorge
Sales
Kwaku
Sales
Liu
Sales
Mateo
Engineering
Nikki
Marketing
Paulo
Marketing
Richard
Marketing
Saanvi
null
Shirley
null
SofÃa
Engineering
Zhang
null
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/05/2024", "domain": "test" }}';
INSERT INTO employee (id, name, manager_id) VALUES
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'SofÃa', 102),
(205, 'Zhang', 104);
CREATE TABLE department (
id INT,
name VARCHAR(50),
manager_id INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/05/2024", "domain": "test" }}';
INSERT INTO department (id, name, manager_id) VALUES
(1, 'HR', 100),
(2, 'Sales', 101),
(3, 'Engineering', 102),
(4, 'Marketing', 103);
SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
INNER JOIN
department d ON e.manager_id = d.manager_id;
SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
LEFT JOIN
department d ON e.manager_id = d.manager_id;
SELECT d.name AS department_name, e.name AS manager_name
FROM
department d
RIGHT JOIN
employee e ON d.manager_id = e.id;
SELECT e.name AS employee_name, d.name AS department_name
FROM
employee e
FULL JOIN
department d ON e.manager_id = d.manager_id;
John
HR
Jorge
Sales
Kwaku
Sales
Liu
Sales
Mateo
Engineering
Nikki
Marketing
Paulo
Marketing
Richard
Marketing
SofÃa
Engineering
Carlos
null
John
HR
Jorge
Sales
Kwaku
Sales
Liu
Sales
Mateo
Engineering
Nikki
Marketing
Paulo
Marketing
Richard
Marketing
Saanvi
null
Shirley
null
SofÃa
Engineering
Zhang
null
HR
Carlos
Sales
John
Engineering
Jorge
Marketing
Kwaku
null
Liu
null
Mateo
null
Nikki
null
Paulo
null
Richard
null
Saanvi
null
Shirley
null
SofÃa
null
Zhang
Carlos
null
John
HR
Jorge
Sales
Kwaku
Sales
Liu
Sales
Mateo
Engineering
Nikki
Marketing
Paulo
Marketing
Richard
Marketing
Saanvi
null
Shirley
null
SofÃa
Engineering
Zhang
null
Column aliases cannot be used in the IN clause of the PIVOT query in Snowflake.
SELECT *
FROM
(SELECT e.manager_id, d.name AS department, e.id AS employee_id
FROM employee e
JOIN department d ON e.manager_id = d.manager_id) AS SourceTable
PIVOT
(
COUNT(employee_id)
FOR department IN ('HR', 'Sales', 'Engineering', 'Marketing')
) AS PivotTable;
100
1
0
0
0
101
0
3
0
0
102
0
0
2
0
103
0
0
0
3
SELECT *
FROM
(SELECT e.manager_id, d.name AS department, e.id AS employee_id
FROM
employee e
JOIN
department d ON e.manager_id = d.manager_id) AS SourceTable
PIVOT
(
COUNT(employee_id)
FOR department IN ('HR', 'Sales', 'Engineering', 'Marketing')
) AS PivotTable;
100
1
0
0
0
101
0
3
0
0
102
0
0
2
0
103
0
0
0
3
Column aliases cannot be used in the IN clause of the UNPIVOT query in Snowflake.
CREATE TABLE count_by_color (quality VARCHAR, red INT, green INT, blue INT);
INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);
SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
cnt FOR color IN (red, green, blue)
);
SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
cnt FOR color IN (red r, green as g, blue)
);
RED
15
RED
35
RED
10
GREEN
20
GREEN
23
BLUE
7
BLUE
40
CREATE TABLE count_by_color (quality VARCHAR, red INT, green INT, blue INT)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "redshift", "convertedOn": "11/05/2024", "domain": "test" }}';
INSERT INTO count_by_color
VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color
VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color
VALUES ('low', 10, 23, NULL);
SELECT *
FROM (SELECT red, green, blue FROM
count_by_color
) UNPIVOT (
cnt FOR color IN (red, green, blue)
);
SELECT *
FROM (SELECT red, green, blue FROM
count_by_color
) UNPIVOT (
cnt FOR color IN (red
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!!
r, green
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!!
as g, blue)
);
RED
15
GREEN
20
BLUE
7
RED
35
BLUE
40
RED
10
GREEN
23
Snowflake supports all types of joins. For more information, see
: Column aliases cannot be used in the IN clause of the PIVOT/UNPIVOT query in Snowflake.