joins in SQLite
SQLite Joins Cheatsheet
Joins are used to combine rows from two or more tables based on a related column between them.
INNER JOIN
Returns records that have matching values in both tables.
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
LEFT JOIN
Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL from the right side.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
RIGHT JOIN
Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL from the left side.
Note: SQLite does not directly support RIGHT JOIN; it can be achieved by swapping table order in LEFT JOIN.
-- Example (SQLite does not support RIGHT JOIN directly.)
-- To achieve a similar result, you can swap the tables and use LEFT JOIN:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
CROSS JOIN
Returns all possible combinations of rows from both tables (Cartesian product).
-- Example
SELECT Customers.CustomerName, Products.ProductName
FROM Customers
CROSS JOIN Products;
FULL OUTER JOIN
Returns all records when there is a match in either left or right table.
Note: SQLite does not directly support FULL OUTER JOIN; it can be achieved using UNION with LEFT JOIN and RIGHT JOIN equivalent.
-- Example (SQLite does not support FULL OUTER JOIN directly.)
-- To achieve a similar result:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
UNION ALL
SELECT Customers.CustomerName, Orders.OrderID
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID IS NULL;