SQL Joins Demo

---

• SQL Joins Demo
  • Examples of SQL Joins on SQLite
  • Image of the Joins

---

Examples of SQL Joins on SQLite

Be sure to turn on headers

.headers on

-- Turn headers on : .headers on

-- Create tables
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Orders;

CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    Name TEXT,
    City TEXT
);

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER,
    Product TEXT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert sample data
INSERT INTO Customers (CustomerID, Name, City) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago'),
(4, 'David', 'Houston');

INSERT INTO Orders (OrderID, CustomerID, Product) VALUES
(1, 1, 'Laptop'),
(2, 2, 'Phone'),
(3, 2, 'Tablet'),
(4, 5, 'Monitor'); -- Note: CustomerID 5 does not exist in Customers

-- Show initial tables
SELECT 'Customers' AS TableName, * FROM Customers;
SELECT 'Orders' AS TableName, * FROM Orders;

-- INNER JOIN: Only matching records
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

-- LEFT JOIN: All customers, even if they have no orders
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

-- RIGHT JOIN workaround: All orders, even if no matching customer (SQLite does not support RIGHT JOIN natively)
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Orders
LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID;

-- FULL OUTER JOIN workaround: Combine LEFT and RIGHT JOIN results
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
UNION
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Orders
LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID;

-- NATURAL JOIN: Automatically joins using columns with the same name
SELECT * FROM Customers
NATURAL JOIN Orders;

-- COMMA JOIN (Cross Join behavior)
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID;

Image of the Joins

Thanks to CL Moffet, 2008 See the blog post here.

---

Back to BACK