Querying Basics
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
andinitial
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 Column Returned
You can't accomplish this with Distinct, you have to rely on Subqueries
Difference vs UNIQUE
there is no difference between
DISTINCT
andUNIQUE
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
# 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 ofLIKE
in PostgreSQL
SELECT * FROM artists WHERE name LIKE 'Barbara%' AND name LIKE '%Hep%';