SQL Joins Examples |¦| Joins in SQL |¦| SQL Tutorial
Video Title: SQL Joins Examples |¦| Joins in SQL |¦| SQL Tutorial
Welcome to the future… again. If you have not seen our previous video on Joins, I would highly recommend you do. The reviews from critics have been overwhelmingly online… If you have seen our previous video, you’ve only begun to discover your SQL powers. Join me, and I will complete your training.
With our combined strength, we can end this destructive conflict, and bring order to the database! Welcome to Mars. You can tell we are on Mars because of the picture of Mars on your screen. The Martian database we will explore has 5 tables: martian, base, visitor, inventory, and supply.
Today we are going to generate several reports for monitoring Martian activity. To make each report, we will need to select data from 2 tables. We will do this using “Joins”. The four flavors of Joins we will use are INNER, LEFT, RIGHT, and FULL. Let’s begin by viewing the data in each table.
The Martian table has 5 columns and 13 rows. The “base_id” tells you the base where the Martian lives, works, and plays. John Carter is currently unassigned. The “super_id” is the Martian ID of the person they report to. If the “super_id” is null, then that Martian is the head of their base,
And they bow to no one. The Base table has 3 columns and 5 rows. There are 4 populated bases, with a Spa & Casino coming soon. Everyone’s betting on when it will open. The “visitor” table has 4 columns and 7 rows, because there are currently 7 visitors on the Red Planet.
The “host_id” is the Martian ID of the person serving as host to the visitor. Every visitor needs a host, and currently there are 2 people without one. We will need to fix that. The “inventory” table keeps track of the supplies in storage at each base.
And the “supply” table lists what is available in the Central Martian Storage Hub. Thank Ares there is an abundance of Famous Ray’s Frozen Pizza. If you’ve never eaten a slice of Ray’s while watching a Martian sunset, then you haven’t lived…. on Mars.
When you have two tables, and there is a value that connects them, you can join them together when you need data from both tables. You do this by writing “SELECT” and the column names you want. If you need to make it clear which table a column is from, you write the table name,
Dot, and column name. Next, the FROM clause. Write the first table, and give it an ALIAS if you are allergic to excessive typing. Next, you join it on table 2 using the “JOIN” keyword. We will give table 2 an alias as well. In the blank, you will specify the TYPE of join.
You write an ON clause to show when to JOIN rows from the two tables. Typically, two tables will be connected by an ID. You finish this query as you would any other select… With an optional WHERE clause… or ORDER BY clause. Here are the choices for the KINDS of joins we will use:
INNER LEFT RIGHT, and FULL (aka FULL OUTER) An INNER join only returns connected rows that satisfy the “ON” criteria. The LEFT join returns all the rows in an “INNER” join, but will also return the remaining rows from the LEFT table, table1.
Since the remaining rows in TABLE1 are not connected to a row from TABLE2, there will be null values in the TABLE2 columns. The RIGHT join is the same as the LEFT join, except this time the unmatched rows from the RIGHT table are included.
And the FULL join includes connected rows, unconnected rows from the LEFT table… and unconnected rows from the RIGHT table. We are now ready to generate our Martian Reports for the Area 51 Martian Monitoring System located on Phobos. The first report will be a visitor report.
This should display the name of each visitor along with the name of their host. The data for this report will come from the “visitor” table and “Martian” table. They are joined by the host_id. This identifies who is hosting the visitor. We want to select the first and last names of the visitor…
…as well as the first and last names of the host. We select this data from the visitor table joined on the martian table. Let’s use the inner join first. The ON clause shows how rows are connected. A visitor row is joined to a martian row when the host_id equals the martian_id. Execute…
Because we used an INNER JOIN, we only see those visitors who have a host. We should be able to see every visitor in the “Visitor Report”, so we will change our query to a LEFT JOIN so that every visitor will be included…even those without a host. Execute… We can still improve things.
For starters, our query is quite verbose. The words “visitor” and “Martian” appear all over the place. To fix this, let’s give “visitor” the alias “v”, and “Martian” the alias “m”. We can now replace “visitor” with “v” throughout the query… And all the occurrences of “martian” with “m”.
The SELECT now fits on a single line. Execute. {small disappointed noise} One other thing. In the output, there are two columns named “first_name”, and two called “last_name”. Which are the visitor names, and which are the Martian names? We can clarify this by giving the column names aliases, too.
Let’s call the “visitor first name” “visitor_fn”… The visitor last name “visitor_ln”… The Martian first name “martian_fn”, and the Martian last name “martian_ln”… Execute The results are now clear as crystal! But I am sad to see the SELECT occupies two rows again. Sigh. The second report will be the “Super Report.”
This should display a list of each Martian and the person they report to. In this example there is a small twist. For each person in the Martian table, the name of their “super” is ALSO in the Martian table. This means we will need to join the “Martian” table with itself. You can relax.
This is perfectly legal in all planetary systems and is called a “Self Join.” We will select all rows… From the “martian” table with an alias of “m” We’ll first do an INNER JOIN on the “martian” table with an alias of “s” for super.
The left table is the “martian”, and the right table is their boss. We want the “super_id” for the Martian to match the “martian_id” of their superior. And order by the martian_id. Execute… There is a problem. We want a report of EVERY Martian. But not every Martian has a superior.
We fix this by doing a LEFT join. Execute… Much better. All Martians are in the report. But the columns are a complete mess. There are even duplicate names! We must fix this immediately! For a clean report, we will only select the name of the Martian with compact aliases…
And the name of their super with aliases “super_fn” and “super_ln”. Execute… Much easier to read. We see a list of all Martians… And the name of their super. If their supervisor’s name is null, that means they are the head of their base.
The next report will be an “Inventory Report” Or the “Base Supply Inventory Report” for short. This has been a lifelong dream of mine – to generate an inventory report. We will generate this report for Base #1. The two tables containing the data we need are the Inventory and Supply tables.
A short SELECT query will return all the supplies at base 1. Execute. This is not terribly helpful, because we would like to know the name of each supply item. Furthermore, we want to know what items they do NOT have in storage.
So we will join this data – just these 6 rows – with the supply table. To do this we need to create a new SELECT. Instead of selecting from the entire inventory table, we will select from the results of our first query.
We need to give these rows a name, and we do that with an alias. We will call it “i” for inventory. This “query inside a query” is creatively called a “subquery.” Next, perform a RIGHT JOIN on the “supply” table, aliased as “s”.
The reason for a RIGHT JOIN is that we want to see ALL available supplies, not just those in stock at base 1. Order by “supply_id” and execute… We now need to clean this up. To help, let’s highlight the columns from the subquery… and highlight the columns from the supply table.
There are two “supply_id” columns. The supply_id column from the inventory subquery is sometimes null. This happens when the item is not in stock. So we will select the supply_id from the supply table. Next, there are two “quantity” columns.
We want to know the quantity at the base, so add that value to our select. Next, select the name and description of each supply item. Execute… A much better report. One improvement I would recommend is to replace null quantities with 0.
I challenge thee to find a way to do this with your database. Our next query is the “No Host Report.” Every visitor to Mars should have a host. But there are some visitors without a host.
We do not want a Martian to have to host more than one visitor, so we will write a query to find all visitors without a host, and all Martians who are available for the job. Select all columns… From the “visitor” table (which we’ll call “v”)
And do a FULL JOIN on the “martian” table (which we’ll call “m”). Two rows are joined when the visitor’s host_id matches the “martian_id”. We use a FULL JOIN because we especially want visitors without a match on the right, and Martians without a matching row in the left table.
With a bit of cleverness, we can filter out people who are already paired up. We will only select rows WHERE “martian_id” is NULL or the “visitor_id” is NULL. Remember, in a FULL JOIN, if a row from one table does not have a match in the other,
Nulls are used to fill in the empty columns. Execute… Then tidy up the columns we select. We will select the names of the visitors… and the names of the available Martians… Execute… Well done us… Your query skills are most impressive. Socratica has taught you well. Indeed you are powerful as I have foreseen.
Now go my viewer, leave me! Call To Action!!
-
Sale!
Wireless WIFI Repeater Extender Amplifier Booster 300Mbps
$29.99$14.99 Add to cartWireless WIFI Repeater Extender Amplifier Booster 300Mbps
Categories: Electronics, Wi-Fi Router, Wireless Wi-Fi Extender Tags: 300Mbps, 802.11N, Amplifier, Booster, Extender, mobile wi-fi booster, Remote, WIFI, Wireless, Wireless WIFI, Wireless WIFI Repeater, Wireless WIFI Repeater Extender, Wireless WIFI Repeater Extender Amplifier, Wireless WIFI Repeater Extender Amplifier Booster, Wireless WIFI Repeater Extender Amplifier Booster 300Mbps$29.99$14.99 -
Sale!
Full RGB Light Design Gaming Headset Headphones with Mic
$24.99$14.99 Add to cartFull RGB Light Design Gaming Headset Headphones with Mic
Categories: Electronics, Gaming, Gaming Headsets Tags: Design, Full, Full RGB Light Design Gaming Headset, Full RGB Light Design Gaming Headset Headphones, Full RGB Light Design Gaming Headset Headphones with Mic, Gamer, Gaming, Gaming Headset Headphones, gaming headset wireless, Headphone, Headphones, Headset, Light, Mic, Package, RGB$24.99$14.99 -
Sale!
Wireless BlueTooth Multi-Device Keyboard Mouse Combo
$39.99$19.99 Add to cartWireless BlueTooth Multi-Device Keyboard Mouse Combo
Categories: Electronics, Gaming, Gaming Keyboards, Keyboard Mouse Combos Tags: Combo, Keyboard, keyboard mouse combos, Mouse, MultiDevice, Set, WireKeyboard Mouse Combo, Wireless, Wireless BlueTooth Keyboard Mouse Combo, Wireless BlueTooth Keyboard Mouse Combos, Wireless BlueTooth Multi-Device Keyboard Mouse Combo, Wireless BlueTooth Multi-Device Keyboard Mouse Combos$39.99$19.99 -
Sale!
High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
$199.99$139.99 Add to cartHigh Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar
Categories: Gaming, Gaming Chairs Tags: Adjustable, Chair, computer chairs, Desk, Executive, Gaming, Girl, Headrest, High, High Back Leather Executive Adjustable Swivel Gaming Chair, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest, High Back Leather Executive Adjustable Swivel Gaming Chair with Headrest and Lumbar, High Back Leather Executive Adjustable Swivel Gaming Chairs, Leather, Lumbar, Office, Racing, Swivel$199.99$139.99 -
Sale!
Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
$29.99$19.99 Select optionsProfessional LED Light Wired Gaming Headphones with Noise Cancelling Microphone
SKU: N/A Categories: Electronics, Gaming, Gaming Headsets Tags: Cancelling, Gaming, Gaming Headphones with Noise Cancelling Microphone, gaming headset, Headphones, Headset, LED, Light, Mic, Microphone, Noise, Professional, Professional LED Light Wired Gaming Headphones, Professional LED Light Wired Gaming Headphones with Noise Cancelling Microphone, Wired, Wired Gaming Headphones, Wired Gaming Headphones with Noise Cancelling Microphone$29.99$19.99 -
Sale!
Gaming Desk with LED Lights USB Power Outlets and Charging Ports
$349.99$249.99 Select optionsGaming Desk with LED Lights USB Power Outlets and Charging Ports
SKU: N/A Categories: Computer Desk, Gaming, Gaming Desk Tags: and Charging Ports, Charging, Desk, Desks, Gaming, gaming desk with led lights, Gaming Desks with LED Lights, Home, LED, Lights, Monitor, Office, Outlets, Port, Power, Room, Stand, USB, USB Power Outlets, White, Workstation$349.99$249.99 -
Sale!
Wired Mixed Backlit Anti-Ghosting Gaming Keyboard
$99.99$79.99 Add to cartWired Mixed Backlit Anti-Ghosting Gaming Keyboard
Categories: Electronics, Gaming, Gaming Keyboards Tags: Antighosting, Backlit, Blue, brown, Gaming, Gaming Keyboard, gaming keyboards, gaming keyboards and mouse, Keyboard, Laptop, Switch, Wired, Wired Mixed Backlit Anti-Ghosting Gaming Keyboard, Wired Mixed Backlit Anti-Ghosting Gaming Keyboards, Wired Mixed Backlit Gaming Keyboard$99.99$79.99 -
Sale!
Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
$119.99$59.99 Add to cartWireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset
Categories: Electronics, Gaming, Gaming Headsets Tags: 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, ANC, Audio, Bluetooth, Cancellation, Ear, Earphone, gaming headset, Headphones, Headset, Hi-Res Over the Ear Headphones Headset, HiRes, Noise, Wireless, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Headphones, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headset, Wireless Bluetooth 5.3 ANC Noise Cancellation Hi-Res Over the Ear Headphones Headsets$119.99$59.99 -
Sale!
Wired Sports Gaming Headset Earbuds with Microphone
$19.99$9.99 Select optionsWired Sports Gaming Headset Earbuds with Microphone
SKU: N/A Categories: Gaming, Gaming Headsets Tags: Accessories, Earbud, Earphone, Earphones, Gaming, gaming headset with microphone, Headphones, Headset, IOS, Microphone, Sports, Wired, Wired Sports Gaming Headset Earbuds, Wired Sports Gaming Headset Earbuds with Microphone, Wired Sports Headset Earbuds$19.99$9.99 -
Sale!
150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
$49.99$29.99 Add to cart150W Universal Multi USB Fast Charger 16 Port MAX Charging Station
Categories: Charging Stations, Electronics Tags: 150W, 150W Charging Station, 150W Universal Multi USB Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Station, 150W Universal Multi USB Fast Charger 16 Port MAX Charging Stations, 150W Universal Multi USB MAX Charging Station, 16 Port MAX Charging Station, 3.5A, Charger, Charging, Fast, laptop charging stations, Max, Multi, Port, Stand, Station, Universal, USB$49.99$29.99
It's official! The Socratica Python Kickstarter was a success! Thank you to all of our supporters. Because of you, many more Python videos coming soon!! 💜🦉
The quality of your content is elite. You guys are the best💗
Extremely well done. It is a little over the top but honestly you are explaining things none of the other vids have so far which were all the difference to me in understanding this concept. Thank you!!
i think it will kool if she would dress as a Martian or an android
This is overwhelming, you should focus on subject.rather than cheap graphcis and explanation. No offense
Honestly thought i'd get lost by the subquery bit but got an understanding of it when thinking about it.
10:53 seeing Elon Musk as a martian doesn't seem so surprising.
It was incredible. Thanks Socratica.
I love these videos so much! Thank you for making studying easier for me!
the amount of effort and great sense of humor this lady has put in , so much appreciated.
Thank you for the SQL video! It was incredibly informative. 😊🙏🎥💻
You're so cool!
0:08 punchline within 10 seconds? Bold move!
freaking love this
Socratica. This is extremely helpful. People one question: My professor said inner join woll cause problems by having cartesian product. How do I solve for it? And how would that ever happen?
this lady is a badass
As an excersice i made this schema with the different tables but for the inventory table… you don't have a primary key right? Because supply_id is a foreign key to the supply table and the base_id is a foreign key to the base table so i guess you need to add another collum yeah idk inventory_shizle so there is a primary key
Please blink 😂
you should name those two videos like part 1 and part 2 because its hard to find this video after the first becuase they have the same name
amazing! thanks for the videos!
OP @FABIO H, Reposting for better reach. SCHEMA AND DATA for this video
/*
JOINS
– INNER
– LEFT
– RIGHT
– FULL
Following are the schema we will use
*/
— CREATE TABLE base (
— base_id SERIAL PRIMARY KEY,
— base_name CHARACTER VARYING,
— founded date
— );
— CREATE TABLE martian (
— martian_id SMALLSERIAL PRIMARY KEY,
— f_name CHARACTER VARYING,
— l_name CHARACTER VARYING,
— base_id INT REFERENCES base,
— super_id INT REFERENCES martian
— );
— CREATE TABLE visitor (
— visitor_id BIGSERIAL PRIMARY KEY,
— host_id INT REFERENCES martian (martian_id),
— f_name CHARACTER VARYING,
— l_name CHARACTER VARYING
— );
— CREATE TABLE supply (
— supply_id SERIAL PRIMARY KEY,
— s_name CHARACTER VARYING,
— description TEXT,
— quantity INTEGER
— );
— CREATE TABLE inventory (
— base_id INTEGER REFERENCES base,
— supply_id INTEGER REFERENCES supply,
— quantity INTEGER
— );
— Inserting data into base table
— INSERT INTO base
— (base_name, founded)
— VALUES
— ('Tharsisland', '2037-06-03'),
— ('Valles Marineris 2.0', '2040-12-01'),
— ('Gale Cratertown', '2041-08-15'),
— ('New New New York', '2042-02-10'),
— ('Olympus Mons Spa & Casino', null);
— Inserting data into martian table
— INSERT INTO martian
— (f_name, l_name, base_id, super_id)
— VALUES
— ('Ray', 'Bradbury', 1, Null),
— ('John', 'Black', 4, 10),
— ('Samuel', 'Hinkson', 4, 2),
— ('Jeff', 'Spender', 1, 9),
— ('Sam', 'Parkhill', 2, 12),
— ('Elma', 'Parkhill', 3, 8),
— ('Melissa', 'Lewis', 1, 1),
— ('Mark', 'Watney', 3, Null),
— ('Beth', 'Johanssen', 1, 1),
— ('Chris', 'Beck', 4, Null),
— ('Nathaniel', 'York', 4, 2),
— ('Elon', 'Musk', 2, Null),
— ('John', 'Carter', Null, 8);
— Insert data into visitor table
— INSERT INTO visitor
— (host_id, f_name, l_name)
— VALUES
— (7, 'George', 'Ambrose'),
— (1, 'Kris', 'Cardenas'),
— (9, 'Priscilla', 'Lane'),
— (11, 'Jane', 'Thornton'),
— (Null, 'Doug', 'Stavenger'),
— (Null, 'Jamie', 'Waterman'),
— (8, 'Martin', 'Humphries');
— Insert data into supply table
— INSERT INTO supply
— (s_name, description, quantity)
— VALUES
— ('Solar Panel', 'Standart 1×1 meter cell.', 912),
— ('Water Filter', 'This takes things out of your water so it''s drinkable.', 6),
— ('Duct Tape', 'A 10 meter roll of duct tape for ALL your repairs.', 951),
— ('Ketchup', 'It''s ketchup…', 206),
— ('Battery Cell', 'Standart 1000 kAh battery cell for power grid (heavy item).', 17),
— ('USB 6.0 Cable', 'Carbon fiber coated / 15 TBps spool.', 42),
— ('Fuzzy Duster', 'It gets dusty around here. Be prepared!', 19),
— ('Mars Bars', 'The ORIGINAL nutrient bar made with the finest bioengineered ingredients.', 3801),
— ('Air Filter', 'Removes 99% of all Martian dust from your ventilation unit.', 23),
— ('Famous Ray''s Frozen Pizza', 'This Martian favorite is covered in all your favorite toppings. 1 flavor only.', 823);
— Insert data into inventory table
— INSERT INTO inventory
— (base_id, supply_id, quantity)
— VALUES
— (1, 1, 8),
— (1, 3, 5),
— (1, 5, 1),
— (1, 6, 2),
— (1, 8, 12),
— (1, 9, 1),
— (2, 4, 5),
— (2, 8, 62),
— (2, 10, 37),
— (3, 2, 11),
— (3, 7, 2),
— (4, 10, 91);
This helps so much. Joins were killing me this made it simple. love it thanks.
223k view before me.
You are genious ♥️
This is very good thanks.