Create and load Customers table
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
country VARCHAR(50)
);
INSERT INTO Customers (customer_id, first_name, last_name, age, country)
VALUES
(1, 'John', 'Doe', 31, 'USA'),
(2, 'Robert', 'Luna', 22, 'USA'),
(3, 'David', 'Robinson', 22, 'UK'),
(4, 'John', 'Reinhardt', 25, 'UK'),
(5, 'Betty', 'Doe', 28, 'UAE');Create and load Orders table
CREATE TABLE Orders ( order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10, 2) ); INSERT INTO Orders (order_id, customer_id, amount) VALUES (1, 1, 300.00), (2, 2, 150.00), (3, 1, 500.00), (4, 3, 200.00);
Verify both tables
SELECT * FROM Customers; SELECT * FROM Orders;
/*----------INNER JOIN ------- */
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country,
Orders.order_id,
Orders.amount
FROM test.Customers
INNER JOIN test.Orders
ON Customers.customer_id = Orders.customer_id;
/*----------LEFT JOIN ------- */
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country,
Orders.order_id,
Orders.amount
FROM test.Customers
LEFT JOIN test.Orders ON Customers.customer_id = Orders.customer_id;
/*----------RIGHT JOIN ------- */
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country,
Orders.order_id,
Orders.amount
FROM test.Customers
RIGHT JOIN test.Orders
ON Customers.customer_id = Orders.customer_id;
/*----------FULL OUTER JOIN ------- */
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country,
Orders.order_id,
Orders.amount
FROM test.Customers
FULL OUTER JOIN test.Orders
ON Customers.customer_id = Orders.customer_id;
/* Workaround */
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country,
Orders.order_id,
Orders.amount
FROM test.Customers
LEFT JOIN test.Orders
ON Customers.customer_id = Orders.customer_id
UNION
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country,
Orders.order_id,
Orders.amount
FROM test.Customers
RIGHT JOIN test.Orders
ON Customers.customer_id = Orders.customer_id;
/*----------LEFT JOIN with no customer------- */
SELECT
Customers.customer_id,
Customers.first_name,
Customers.country
FROM test.Customers
LEFT JOIN test.Orders
ON Customers.customer_id = Orders.customer_id
WHERE Orders.order_id IS NULL;