SQL Joins Explained |¦| Joins in SQL |¦| SQL Tutorial
![*](https://i0.wp.com/allprowebdesigns.com/wp-content/uploads/2023/12/1703159759_maxresdefault.jpg?resize=840%2C430&ssl=1)
Video Title: SQL Joins Explained |¦| Joins in SQL |¦| SQL Tutorial
Welcome to the future. This timeline is amazing! The global hyperloop network is finally complete. The bioengineered glow-in-the-dark Redwood Forest in the Sahara is a vacation paradise. And best of all, we have colonies throughout the Solar System. One thing that might surprise
You about the future is that everyone still uses SQL. I didn’t see that coming. So to prepare you for the future, we now explore an essential part of SQL: Joins. The database we will be using to learn about joins has 5 Tables: martian, base, visitor, inventory, and supply.
The Martian table is a list of all people living on the Red Planet. After all, if you are living on Mars, you are no longer an Earthling – you’re a Martian! The “base” table holds information on all the different habitats on Planet #4. In addition to serving as a stopover for people
Visiting the outer planets, Mars is now a tourist destination. Every visitor to Mars is tracked in the “visitor” table. The “inventory” table keeps track of the supplies at each base. And the “supply” table shows what is available at the central Martian distribution center.
Let’s take a closer look at the “Martian” and “Base” tables. Here are the columns in the “Martian” table… The “super_id” is the “ID” of the Martian they report to. And here are the columns in the “Base” table. These two tables are connected, because each “Martian” has a “base_id” identifying
Where they live and work. Suppose you need to generate a report listing the full names of all Martians and the name of their home base. How would you do it? The Martian name is in the “Martian” table, while the “base” name is in the “Base” table.
The solution is to JOIN these two tables together by the “base_id.” To begin, look at the DATA in the Martian table… and the Base table. Let us visualize how to join these tables together by the “base_id.” Take the first row from the “Martian” table…
Find the row in the Base table with the matching base_id… And then join them together to make a new, larger row. You continue doing this row… by row… by row… This brings us to John Carter. He does not have a base_id, so what do we
Do? Do we include him in the join? Or leave him out? This is a very important question. The answer depends on the TYPE of join you do. For now, we will leave John Carter out. The result is 12 rows. Each row is a combination of data from both tables.
Having joined these two tables, you can select data from these rows like an ordinary SELECT query. By the way, notice that there is currently no Martian at the Olympus Mons Spa & Casino. This is because the staff is en route from the Moon and will not arrive for a few more weeks.
Here’s how to write a join using SQL. Begin with SELECT * so we can see all columns. This will help in understanding the join. Next, specify the two tables. FROM martian … INNER JOIN base. We will call “martian” the left table, and “base” the right table. The “JOIN” keyword indicates we will be
Joining these two tables. We’ll explain the INNER keyword in just a moment. Next, specify HOW to connect two rows from these tables with an ON clause. We only want to connect a martian row with a base row if they have the same base_id. In the “ON” clause, you have to specify
Both the table name… and column name. This makes it clear which table each column is from. Now execute… The result contains all the columns from the Martian table… And all the columns from the Base table. The rows are matched by base_id.
John Carter and the Olympus Mons Spa & Casino do not appear in the result. This is because neither one had a match in the other table. We can now update this query to return only those columns we need for our report. First name… last name… and base name… Execute. Report complete.
When joining the tables, we encountered two problematic rows. John Carter is in the Martian table, but his base is unknown. And the Olympus Mons Spa & Casino currently has no Martians. So how do you tell SQL whether or not to include rows from a table that do not have a match
In the other? Hmm?? We have two tables, and we have two choices per table. Include rows without a match or exclude them. This means there are 4 total options. Spoiler Alert: SQL has a JOIN for each of the four options. Let’s visit them now. We return to the “Martian” and “Base” tables.
We will call the “Martian” table the left table, and “Base” the right table. Each table has a row with no match in the other table. Here is the syntax for joining these two tables: Write SELECT followed by the columns you want FROM martian… blank… JOIN base
ON martian.base_id = base.base_id The ON clause specifies HOW rows from the two tables will be connected. Because this is a SELECT query, you can also have WHERE and ORDER clauses. All that remains is to fill in the blank and specify what kind of JOIN to perform.
We will learn about four types of joins: INNER.. LEFT .. RIGHT .. and FULL. An INNER JOIN will only return connected rows when there is a matching base_id in both tables. A LEFT JOIN will return EVERY row from the left table, even if there is no matching row
In the right table. If a Martian row has no match in the Base table, it will return nulls for ALL the columns in the right table. A RIGHT JOIN will return EVERY row from the right table, even when there isn’t a match
In the left table. When a Base row does not have a matching Martian row, null values will be used for ALL columns in the left table. And finally there is the FULL JOIN. This is sometimes called a FULL OUTER JOIN.
The FULL JOIN is a combination of the LEFT JOIN and RIGHT JOIN. A full join returns every row from the left table and every row from the right table. When the base_ids match, the rows are connected. But when there isn’t a match, the row is
Still included in the JOIN with nulls for the columns from the other table. A common way to visualize the difference between these 4 kinds of joins is with Venn Diagrams. The Inner Join… Left Join… Right Join… and Full Join… The inner join diagram highlights how only rows with matching columns are connected.
The left join diagram shows that all rows in the left table are returned, even those without a match on the right. The right join is the mirror image of the left join. And the full join shows that all rows from both tables are returned. When two rows match, they are connected.
Otherwise, nulls will fill in the gaps. I just received a Dear John letter. It’s especially sad because my name isn’t even John! It reads, “Dear John. Not every database supports the four joins you discussed. For example, MySql and SQLite do not support FULL JOINs. Sincerely, R. E. Joinder”
I will not be deterred. We are going to see examples of these four kinds of joins using Postgres. Maybe the other databases will… join us? Before seeing examples for INNER, LEFT, RIGHT, and FULL joins I would like to revisit our first query. SELECT * FROM martian INNER JOIN base ON martian.base_id = base.base_id
Execute… Notice how the output has TWO columns named base_id. The first one is from the “martian” table, and the second one is from the “base” table. What if instead of selecting all columns from both tables, we only select the martian_id, base_id, and base_name. Execute.
We get an error. This is because it is ambiguous which base_id column we want. Is it the base_id from the martian table or base table? To avoid ambiguity, you specify the table AND column name in the SELECT clause. We will select the “base_id” from the “base” table… Execute… Success…
Repeatedly typing the table name can make queries a bit word-y, and hands a bit sore-y We can fix this by creating an “alias” for each table name To do this, use the “AS” keyword. If we write “FROM martian AS m”, then ‘m’ is now an alias for the table name “martian”
And let us give the “base” table an alias of “b” Now everywhere in our query we can use “m” instead of the table name “martian”, and use “b” instead of the table name “base”. The “ON” clause becomes nicer and shorter: And the SELECT clause is more compact as well
Now that you have properly been introduced to Joins, it is time to see some examples. Not just one or two examples, mind you, but a LOT of examples. But not too many! We don’t want you to feel bored. Somewhere between not quite enough and way too many. That is
How many examples we are going to see…. In our next video. So JOIN me in the next video as we boldly go where no one has gone before… Ok, that’s a lie. If you look at the number of views on the next video, you will see exactly how many people have
Gone before you.
-
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!! 💜🦉
I'm so thankful for this tutorial. I've finally understood the process and I've been just joining tables when I can just because I'm so happy I can do it. Thank you again!
THIS IS EXACTLY WHAT I WAS LOOKING FOR, the tables so nicely presented instead of starting at sql server no clue what to type omg
Absolutely awesome! Thank you!
OMG this is so cool! Thx 🙂
this is amazing and deserves clapping
My prof spent an hour, and he succeeded in confusing 80+ students in the lecture. You spent 10 min, and it makes so much sense!
Acting, jokes, delivery of information, everything here is magnificent
I'm amazed.
ok, but how do I select only the data that has no match in one of the tables? Like the left join without what's in inner join? In math it would be AB, in your tables it would be for example the base_name with the id 5 from the table on the right at 5:16 as it isn't included in the table on the left
GG what of the most creative video thanks a lot
This video has a 90s sci fi retro gaming vibe… I would spend 100s of hours watching lectures if they were delivered like this
thank you
pretty good
I understand it! Thank you so much! 💙💙
I'm watching this video and boy am I so blessed. Thank you for spending the time to create this video. God bless you.
Lmao you're good and funny
Wow this videos editing and everything really does make youtube a better place
Love this explanation
she is so funny!
Wow, great way of teaching! Wasn't hard to follow, and the theme makes it extra interesting. Feels like the robotic NPCs of bethesda games, in a good way! Liked and subbed!
FINALLY having my AHA moment 🫠 thank you!
Very well explained. Thanks
perfectly explained . Thank you
Finally a clear explanation of joins!
A fried shared this video as I was struggling to grasp the concept and you just made my life way simple. Thank you very much.
Dang this was good and helpful
GENIUS
lmao, this is an amazing video, your teaching style is on point, thanks a lot
Very good video.
There is a mistake in the animation of joining 2 tables at video time 2:20. Joining of the martian_id = 6 (base_id = 3) starts with joining base_id = 1 of the base table which is wrong (should be with base_id = 3). When the joined lines are added to the bottom of the final table the error is corrected.
I love you for your creativity and visual way of explaining. End of transmission_
Fantastic video!!
This is by far the most entertaining way I've learned anything as a new software developer–thanks for all of your hard work! I LOVE this! The information was clear and plain, and you were hilarious 🙂 Can't wait to watch more of your videos, lol…thanks!
Take a bow, the creative team behind this tutorial. You people deserve an Oscar for the R. E. Joinder thingy!
I just can't appreciate more! Supeb 🚀🚀🚀
Best video on SQL JOINS!
Heh, Ray Bradbury and John Carter, Mark Watney… (Though he was more of a visitor…)
One of the best explanations I have ever met
What a dork 😂🤣
As someone who struggels with ADHD, this was awesome! This helped me so much! Tank you
So with a FULL JOIN is that when you end up with a lot of NULL entries?
EXCELLENT VIDEO THANK YOU
What a masterpiece
thank you so, much !
ThI is very insightful. Thank you for this video. Very clear and easy to understand ❤
Perfect Socratica! Perfect.