Common Relational DB SQL concepts for quick revisit, recapped mainly from
w3school and go/Bard, not every DB supports
the listed syntax, so please use them accordingly.
SQL Clauses Layout Order
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT column_name(s) FROM table1 JOIN table2 ON table1.column = table2.column WHEREcondition GROUPBY column_name(s) HAVINGcondition ORDERBY column_name(s);
-- you can use multiple tables, it is the same as inner join SELECT column_name(s) FROM table1, table2 WHEREcondition GROUPBY column_name(s) HAVINGcondition ORDERBY column_name(s);
Distinct
1 2 3 4 5 6 7
SELECTCOUNT(DISTINCT Name) FROM Customers;
-- Distinct applies on the combination in SELECT, not just the first column SELECTDISTINCT collectors.first_name, collectors.last_name FROM collectors JOIN sales ON collectors.id = sales.collector_id;
-- Single quote for text value SELECT*FROM Customers WHERE Country='Mexico';
-- No quote around number value SELECT*FROM Customers WHERE CustomerID=1;
Using Parenthesis
1 2
SELECT*FROM Customers WHERE Country='Germany'AND (City='Berlin'OR City='München');
Min and Max
1 2
SELECTMAX(Price) AS LargestPrice FROM Products;
Count, Avg and Sum
1 2
SELECTCOUNT(ProductID) FROM Products;
Order By
1 2 3 4 5 6 7
SELECT*FROM Customers ORDERBY Country ASC, CustomerName DESC;
-- order by field can be irrelevant from select SELECT Address FROM Customers ORDERBY CustomerName ASC
NULL
Not every DB supports NULL for empty values, be aware.
1 2 3 4 5 6 7
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address ISNULL;
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address ISNOT NULL;
Limit
1 2
SELECT*FROM Customers LIMIT 3;
In
The IN operator is a shorthand for multiple OR conditions.
1 2 3 4 5 6
SELECT*FROM Customers WHERE Country NOTIN ('Germany', 'France', 'UK');
-- subquery SELECT*FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
Alias
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
-- New column with a Literal value SELECT'Customer'AS Cus, ContactName, City, Country FROM Customers
-- CONCAT is a SQL function SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address FROM Customers;
-- you can omit AS for table alias SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn'AND c.CustomerID=o.CustomerID; -- the same as SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers c, Orders o WHERE c.CustomerName='Around the Horn'AND c.CustomerID=o.CustomerID;
Joins
(INNER) JOIN: Returns records that have matching values in all tables.
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table.
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table.
SELECT column_name(s) FROM table1 INNERJOIN table2 ON table1.column_name = table2.column_name; -- the same as JOIN SELECT column_name(s) FROM table1 JOIN table2 ON table1.column_name = table2.column_name;
-- Inner join 3 tables SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ( ( Orders INNERJOIN Customers ON Orders.CustomerID = Customers.CustomerID ) INNERJOIN Shippers ON Orders.ShipperID = Shippers.ShipperID );
You can use USING to subsitute ON if the columns name are the same, please
see here
1 2 3 4
SELECT column_name(s) FROM table1 INNERJOIN table2 USING (id, score);
Left Join
1 2 3 4 5 6 7 8 9 10 11 12
-- Show both CustomerID can better explain the left join SELECT Customers.CustomerName, Customers.CustomerID as id1, Orders.CustomerID as id2, Orders.OrderID FROM Customers LEFTJOIN Orders ON Customers.CustomerID = Orders.CustomerID ORDERBY Customers.CustomerName;
The LEFT JOIN keyword returns all records from the left table (Customers), even
if there are no matches in the right table (Orders) and if there is no that
CustomerID in Orders table, you get null for the Orders selected fields.
Right Join
1 2 3 4 5 6 7 8 9
SELECT Orders.OrderID, Orders.EmployeeID as id1, Employees.EmployeeID as id2, Employees.LastName, Employees.FirstName FROM Orders RIGHTJOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDERBY Orders.OrderID;
The RIGHT JOIN keyword returns all records from the right table (Employees),
even if there are no matches in the left table (Orders).
Full Join
1 2 3 4
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULLOUTERJOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDERBY Customers.CustomerName;
The FULL OUTER JOIN keyword returns all matching records from both tables
whether the other table matches or not. So, if there are rows in “Customers”
that do not have matches in “Orders”, or if there are rows in “Orders” that do
not have matches in “Customers”, those rows will be listed as well.
Self Join
1 2 3 4 5 6 7 8 9 10 11 12 13
-- there will be duplicates introduced by switch CustomerName in A and B tables SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B -- can omit AS in table alias WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDERBY A.City;
Union
The UNION operator is used to combine the result-set of two or more SELECT
statements.
Every SELECT statement within UNION must have the same number of columns.
The columns must also have similar data types.
The columns in every SELECT statement must also be in the same order.
1 2 3 4 5 6 7 8
-- 'Customer' and 'Supplier' are literal value under the 'Type' column -- here is used to distinguish the rows are from which select SELECT'Customer'AS Type, ContactName, City, Country FROM Customers -- UNION ALL -- can have duplicates UNION-- union distinct row only SELECT'Supplier', ContactName, City, Country FROM Suppliers;
Group By
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(),
MIN(), SUM(), AVG()) to group the result-set by one or more columns.
1 2 3 4 5 6 7 8 9 10 11 12
SELECT column_name(s) FROM table_name WHEREcondition GROUPBY column_name(s) ORDERBY column_name(s);
-- Example SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFTJOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUPBY ShipperName;
Having
The HAVING clause was added to SQL because the WHERE keyword cannot be used with
aggregate functions.
1 2 3 4 5 6 7 8 9 10 11 12 13
SELECT column_name(s) FROM table_name WHEREcondition GROUPBY column_name(s) HAVINGcondition ORDERBY column_name(s);
-- Example SELECTCOUNT(CustomerID) as CustomerNumber, Country FROM Customers GROUPBY Country HAVING CustomerNumber >5 ORDERBY CustomerNumber;
Exist
The EXISTS operator is used to test for the existence of any record in a
subquery.
SELECT column_name(s) FROM table_name WHEREEXISTS ( SELECT column_name FROM table_name WHEREcondition );
-- Example SELECT SupplierName FROM Suppliers WHEREEXISTS ( SELECT ProductName FROM Products -- Suppliers is from outer layer WHERE Products.SupplierID = Suppliers.supplierID AND Price <20 );
Any and ALL
The ANY and ALL operators allow you to perform a comparison between a single
column value and a range of other values.
1 2 3 4 5 6 7 8 9 10 11 12 13 14
SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHEREcondition);
-- Example SELECT ProductName FROM Products WHERE ProductID =ANY-- can be rewritten as IN operator (SELECT ProductID FROM OrderDetails WHERE Quantity =10);
Subquery
This is how you can boost your query, basically you can use subquery in below
clauses:
-- SELECT clause SELECT first_name, last_name, ( -- inner query runs for every row of collectors table SELECTcount(*) AS paintings FROM sales WHERE collectors.id = sales.collector_id -- correlated with outer query ) FROM collectors;
-- WHERE clause SELECT customer_name FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_date >'2023-03-08' );
-- HAVING clause SELECT product_category, average_price FROM products GROUPBY product_category -- no where as where cannot use aggragate functions HAVING average_price > ( SELECTAVG(price) FROM products WHERE product_category ='Electronics' );
-- FROM clause SELECT product_name, quantity FROM ( /* Generate a new table from subquery */ SELECT product_name, SUM(quantity) AS quantity FROM order_items GROUPBY product_name );
-- with JOIN, the example no special meaning. SELECT res.region,res.availability_zone, res.machine_type, res.billing_account_id, res.reservation_code FROM ( ( SELECTDISTINCT r.earmark.region, r.earmark.availability_zone, r.earmark.machine_type, r.earmark.billing_account_id, r.earmark.reservation_code FROM Reservations AS r WHERE r.ProvenanceMetadata.version_state ='LATEST'AND r.earmark.reservation_code!="" ) AS res JOIN ( SELECTdistinct p.PlacementGroup.region, p.PlacementGroup.availability_zone, p.PlacementGroup.machine_type FROM PlacementGroupCapacity AS p WHERE p.ProvenanceMetadata.version_state ='LATEST'AND p.PlacementGroup.pg_pool="" ) AS pgc ON res.region=pgc.region AND res.availability_zone=pgc.availability_zone AND res.machine_type=pgc.machine_type );