SQL Journey: Blog #6

In this blog, I will be explaining what I understand about "outer join". Based on the given lecture, it means that it is a method of joining the specific data from two related tables EVEN if there are missing or incomplete entries in one of the two related tables. It is different from an inner join since an inner join will only display the rows if there is a match between the common identifiers i.e. student_id. According to w3schools, it can be best visualize using the following Venn diagrams.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

(INNER) JOIN: Returns records that have matching values in both 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

Here is the next challenge entitled, "Customer's Orders" and the given data:

CREATE TABLE customers (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    name TEXT,

    email TEXT);

    

INSERT INTO customers (name, email) VALUES ("Doctor Who", "doctorwho@timelords.com");

INSERT INTO customers (name, email) VALUES ("Harry Potter", "harry@potter.com");

INSERT INTO customers (name, email) VALUES ("Captain Awesome", "captain@awesome.com");

CREATE TABLE orders (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    customer_id INTEGER,

    item TEXT,

    price REAL);

INSERT INTO orders (customer_id, item, price)

    VALUES (1, "Sonic Screwdriver", 1000.00);

INSERT INTO orders (customer_id, item, price)

    VALUES (2, "High Quality Broomstick", 40.00);

INSERT INTO orders (customer_id, item, price)

    VALUES (1, "TARDIS", 1000000.00);

Step 1

We've created a database for customers and their orders. Not all of the customers have made orders, however. Come up with a query that lists the name and email of every customer followed by the item and price of orders they've made. Use a LEFT OUTER JOIN so that a customer is listed even if they've made no orders, and don't add any ORDER BY.

Code:

SELECT customers.name, customers.email,orders.item, orders. Price FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id;

Query Results:

name	email	item	price
Doctor Who	doctorwho@timelords.com	Sonic Screwdriver	1000
Doctor Who	doctorwho@timelords.com	TARDIS	1000000
Harry Potter	harry@potter.com	High Quality Broomstick	40
Captain Awesome	captain@awesome.com	NULL	NULL

Step 2

Now, create another query that will result in one row per each customer, with their name, email, and total amount of money they've spent on orders. Sort the rows according to the total money spent, from the most spent to the least spent.
(Tip: You should always GROUP BY on the column that is most likely to be unique in a row.)

Code:

SELECT customers.name, customers.email, SUM(orders.price) FROM customers LEFT OUTER JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id ORDER BY SUM(orders.price) DESC;

Query Results:

name	email	SUM(orders.price)
Doctor Who	doctorwho@timelords.com	1001000
Harry Potter	harry@potter.com	40
Captain Awesome	captain@awesome.com	NULL

Reference:

Retrieved from https://www.w3schools.com/sql/sql_join.asp#:~:text=(INNER)%20JOIN%20%3A%20Returns%20records,records%20from%20the%20left%20table on 22 August 2023 at 3:47AM PST