Data Query Language (DQL) Statement Reference

[ WITH ... ]
SELECT ...
    [ EXCEPT(...) ]
[ FROM ...
    [ JOIN ... ] ]
[ WHERE ... ]
[ GROUP BY ...
    [ HAVING ... ] ]
[ ORDER BY ... ]
[ LIMIT ... ]
[ OFFSET ... ]
[ INTERSECT ... ]
[ EXCEPT ... ]
[ UNION ... ] ]
[ USING ... ]
[ TRACE ... ]
[ TAG ... ]

WITH
    <cte_name> [ ( <cte_column> [ ,... ] ) ]
    AS ( <sub_query> )
<select_query>

WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget;

SELECT [ ALL | DISTINCT ]
    [ * [ EXCEPT ( column_name [ , ... ] ) ]
    | <select_list_entry> [ , ... ] ]
    [ <from_clause> ]

<select_list_entry> ::=
    column_name | expression [ AS new_name ]

SELECT * FROM movies LIMIT 5;

SELECT *
EXCEPT (movie_id, runtime, vote_average, vote_count)
FROM movies
LIMIT 5;

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
    );

INSERT INTO products (product_id, product_name, price) VALUES
    (1, 'Laptop', 1000.00),
    (2, 'Tablet', 500.00),
    (3, 'Smartphone', 800.00),
    (4, 'Monitor', 300.00);

WITH DiscountedPrices AS (
    SELECT
        product_id,
        product_name,
        price,
        price * 0.90 AS discounted_price,
        price * 0.90 * 1.05 AS total_price_after_tax
    FROM
        products
)
SELECT
    product_id,
    product_name,
    price,
    discounted_price,
    total_price_after_tax
FROM
    DiscountedPrices;

SELECT
    product_id,
    product_name,
    price,
    price * 0.90 AS discounted_price,
    discounted_price * 1.05 AS total_price_after_tax
FROM
    products;

<select_clause> FROM
    { table_name | ( <sub_query> ) | ( <values_clause> ) } [ ,... ]
    [ <join_clause> [ ,... ] ]

<select_query>
    FROM <table_reference1> <join_operation> <table_reference2>
        ON table1_column <boolean_operator> table2_column

<join_operation> ::=
    { INNER JOIN
    | LEFT [ OUTER ] JOIN
    | RIGHT [ OUTER ] JOIN
    | FULL [ OUTER ] JOIN
    | CROSS JOIN
    | SEMI JOIN
    | ANTI JOIN }

SELECT game_name,
    genre_name
FROM video_games.game
    INNER JOIN video_games.genre ON game.genre_id = genre.id;

SELECT game_name,
    genre_name
FROM video_games.game
    LEFT OUTER JOIN video_games.genre ON game.genre_id = genre.id;

SELECT game_name,
    genre_name
FROM video_games.game
    RIGHT OUTER JOIN video_games.genre ON game.genre_id = genre.id;

SELECT game_name,
    genre_name
FROM video_games.game
    FULL OUTER JOIN video_games.genre ON game.genre_id = genre.id;

SELECT game_name,
    genre_name
FROM video_games.genre
    CROSS JOIN video_games.game;

SELECT genre_name,
    id
FROM video_games.genre
    SEMI JOIN video_games.game ON genre.id = game.genre_id;

SELECT game_name
FROM video_games.game
    ANTI JOIN video_games.genre ON game.genre_id = genre.id;

<select_query>
    WHERE { column_name <filter_condition> filter_value |
    [ NOT ] EXISTS ( <sub_query> ) }

<filter_condition> ::=
  { =
    | ==
    | <>
    | !=
    | [ NOT ] EQUALS
    | <
    | <=
    | >
    | >=
    | [ NOT ] IN
    | [ NOT ] LIKE
    | [ NOT ] SIMILAR TO
    | BETWEEN
    | FOR SOME
    | FOR ALL
    | IS [ NOT ] NULL
    | IS [ NOT ] DISTINCT FROM
  }

CREATE TABLE departments (
    id INT,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department_id INT
);

INSERT INTO departments (id, name) VALUES
    (1, 'HR'),
    (2, 'IT'),
    (3, 'Marketing');

INSERT INTO employees (id, name, department_id) VALUES
    (1, 'Alice', 1),
    (2, 'Bob', 2),
    (3, 'Charlie', 2),
    (4, 'David', 4);

SELECT name
FROM employees AS e
WHERE EXISTS (
    SELECT *
    FROM departments AS d
    WHERE e.department_id = d.id
);

Bob
Charlie
Alice

SELECT name
FROM departments AS d
WHERE EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.id
);

HR
IT

SELECT *
FROM departments d
WHERE d.id != 3
AND EXISTS (
    SELECT *
    FROM employees e
    WHERE e.department_id < 4
);

HR
IT

SELECT name
FROM employees AS e
WHERE NOT EXISTS (
    SELECT *
    FROM departments AS d
    WHERE e.department_id = d.id
);

WHERE string1 SIMILAR TO string2

ARRAY_LENGTH(array_col) > 0 AND FOR_ALL(array_col) LIKE '%ocient%

FOR_SOME(array_col) IS NULL

SELECT <select_list_clause> FROM table_name
    GROUP BY { column_name | expression | integer } [ , ... ]
    [ HAVING <filter_condition> ]

SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
ORDER BY 1 ASC;

GROUP BY column_name HAVING <filter_condition>

SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
HAVING SUM(budget) > 100000000
ORDER BY 1 ASC;

ORDER BY { column_position | column_name } [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ] [ , ... ]

SELECT release_date, title
FROM movies
ORDER BY release_date DESC;

<select_query> LIMIT limit_number

SELECT title FROM movies LIMIT 3;

Minnows
CGI Why
Billy the Killy

SELECT c1 FROM sys.dummy10 LIMIT 1+2;

1
2
3

<select_query> OFFSET offset_number

SELECT release_date,
    title
FROM movies
ORDER BY release_date OFFSET 6;

SELECT c1 FROM sys.dummy10 OFFSET 3+4;

8
9
10

<select_query1> INTERSECT [ ALL | DISTINCT ] <select_query2>

SELECT *
FROM movies
WHERE revenue > 1000000000
INTERSECT
SELECT *
FROM movies
WHERE budget > 20000000;

<select_query1> EXCEPT [ ALL | DISTINCT ] <select_query2>

SELECT *
FROM movies
EXCEPT
SELECT *
FROM movies
WHERE budget > 20000000;

<select_query1> UNION [ ALL | DISTINCT ] <select_query2> [ ... ]

SELECT title,
    popularity,
    vote_average
FROM movie
WHERE popularity > 800
UNION
SELECT title,
    popularity,
    vote_average
FROM movies.movie
WHERE vote_average > 7.5;

<select_query>
USING
    [ SCHEDULING_PRIORITY = priority_value ]
    [ MAX_ROWS_RETURNED = max_rows ]
    [ MAX_ELAPSED_TIME = max_elapsed_time ]
    [ MAX_TEMP_DISK_USAGE = max_temp_disk_usage ]
    [ CACHE_MAX_TIME = cache_max_time ]
    [ CACHE_MAX_BYTES = cache_max_bytes ]
    [ SERVICE CLASS service_class ]

SELECT * FROM sys.dummy10
USING
    SCHEDULING_PRIORITY = 1.0
    MAX_ROWS_RETURNED = 10
    MAX_ELAPSED_TIME = 10
    MAX_TEMP_DISK_USAGE = 10;

c1
-----------
1
2
3
4
5
6
7
8
9
10

<select_query> TRACE
    [ FREQUENCY frequency_int ]
    [ RESOLUTION resolution_int ]

SELECT * FROM sys.dummy10 TRACE;

<select_query> <tag> [ ... ]

<tag> ::= TAG <tag_identifier>

SELECT COUNT(*) FROM sys.dummy1 TAG count1;

SELECT device_model
FROM system.adtech_flat
LIMIT 1
TAG device_model
TAG phones;

WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
    TAG average_budget
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget
TAG overall_budget;

SELECT '\my\directory\path';

SELECT e'\my\directory\path';

SELECT e'\\my\\directory\\path';

SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz',
    '\w+'
    );

SELECT REGEXP_SUBSTR(
    'abcdefghijklmnopqrstuvwxyz',
    e'\w+'
    );