SELECT

You must capitalize the verbs (e.g. WHERE) and close a sql statement with a semicolon ;

Mixed-case Column Name

When you have mixed case table_Name or column_Name, you have to wrap it in double quotations ("")

• When you are running a value match, use a single quotation

SELECT * FROM table_name;

# mixed case table name
SELECT * FROM "Mixed_Case_Table_name";

# mixed case column name
SELECT * FROM "Mixed_Case_Table"
WHERE "mixedcase_Column" = '1'; # single quote for value matching

DISTINCT

Basic Use Case

Distinct returns unique values on a result set

# users table
name | initial
------------
Johnson | A
John | A
John | B

# query using distinct
SELECT DISTINCT t1.name FROM users t1;

# result table
name
----
Johnson
John

Distinct is used to return unique records across the entire result set, not just on one column

• Meaning, if you are querying unique values on name and initial columns, both column values are evaluated for uniqueness

# query using distinct on two columns
SELECT DISTINCT t1.name, t1.initial FROM users t1;

# result
name | initial
------------
Johnson | A
John | A
John | B

Using with Aggregates

Return the number of unique names in users table

SELECT COUNT(DISTINCT t1.name) FROM users t1;

Caveats

Apply Distinct on One Column with Multiple Columns Returned

You can't accomplish this with Distinct, you have to rely on Subqueries

Difference vs UNIQUE

there is no difference between DISTINCT and UNIQUE

WHERE Conditions

WHERE clause is used to filter results returned by the SELECT statement

Comparison operators can be used to express the condition

WHERE =, >, <, >=, <=, <>, !=, AND, OR

• <> is same as !=

AND and OR

# AND
SELECT * FROM table_name WHERE "Age" = 25 AND "Level" > 2;

# OR
SELECT * FROM table_name WHERE "Age" = 25 OR "Level" > 2;

IN

IN will return all records (left hand expression) that match any of the values passed against the right hand expression

# return any records that match "1" or "2"
SELECT * FROM table_name WHERE "Id" IN (1, 2);

NOT IN

Source

# lengthy version of excluding multiple values
SELECT * 
FROM Sales.Invoices 
WHERE LastEditedBy <> 11
  AND LastEditedBy <> 17
  AND LastEditedBy <> 13;

# synonymous to excluding multiple values with one query
SELECT * 
FROM Sales.Invoices 
WHERE LastEditedBy NOT IN (11,17,13);

ALL

ALL will return true if the operation is true for all values in the range. If there is one value that does not match the criteria, the query will return nothing.

SELECT ProductName FROM Products
WHERE ProductID = ALL(
  SELECT ProductID FROM OrderDetails WHERE Quantity > 10
);

ANY

Returns boolean as a result

• Returns True when any of the subquery values meet the condition

Usefulness with Array record

You have to use ANY when comparing an array value (e.g. uuid compared to uuid[] would be an include which is any)

SELECT t1.* FROM "Cars" t1
INNER JOIN "Dealership" t2 
  ON t2._vehicleBrand = ANY(t1."carBrands")
WHERE t2."State" = 'NewYork' AND t2."zipCode" = '11101'

LIKE

Wildcard statement for fuzzy matching

• ILIKE is a case-insensitive version of LIKE in PostgreSQL

SELECT * FROM artists
WHERE name LIKE 'Barbara%'
AND name LIKE '%Hep%';