hey everybody welcome back again in today's video I'm going to be explaining joins in MySQL a join is a clause that is used to combine rows from two or more tables based on a related column between them such as a foreign key here's an example I have two tables a table of transactions and a table of customers think of these two tables as a Venn diagram transactions will be the table on the left customers will be the table on the right whatever data they have in common is the middle part of our Venn diagram for
my demonstration to make more sense I will need to add a few extra rows feel free to pause the video if you need to catch up I will insert into transactions a new row the amount is one dollar the customer ID is null so not all transactions can have a customer ID that foreign key here's a scenario suppose that somebody comes in pays for a soda with cash well we wouldn't have a customer ID right if a customer instead paid with a credit card we could track who that customer was there may be a customer
ID who initiated that credit card charge I'm going to insert this row and here's our new transactions table not all rows have a customer ID then let's add one more customer insert into customers first name last name will be poppy Puff let's select all of our customers now not all transactions have a customer ID and not all customers have ever initiated a transaction they could be registered as a customer but they have never actually bought anything yet using joins let's take a look at what data these two tables have in common we'll discuss inner joins
left joins and right joins let's begin with an inner join to create an inner join between these two tables you would type select all from which table would you like to be on the left think of that Venn diagram our transactions table will be on the left from transactions inner join whatever table you would like to be on the right in this case customers on we're going to join these two tables together by the foreign key from transactions we'll take transactions.the name of the foreign key column which was customer ID equals the table on the
right dot the name of the primary key column which was customer ID then we will execute the statement and here's our new table we have created an inner join from these two tables based solely on what they have in common if you remember we don't have that Row for that transaction for one dollar that doesn't have a customer ID as well as our customer poppy puff there's no role for her in this table what we're telling MySQL is to select all rows from these two tables that have matching customer ideas so that's why some data
was excluded one thing you could do with a join this applies to left joins and right joins as well you don't necessarily need to display every single column from both tables you can select specific columns let's select our transaction ID the amount the first name then the last name this would make it a lot easier to find the first and last name of who initiated a transaction at a given time we know who bought which order so that is an inner join join together any matching rows based on some Link in this case we're joining
these two tables together by their customer ID now with the left join we are going to display everything from the table on the left but let's select all with the left join we will display everything from the table on the left our transactions however if there is a matching customer ID pull in any relevant data from the table on the right even though there's no customer ID with this latest row we're still going to display it with a left join but there's no data to pull in from the right table because there's no registered customer
ID with the right join we will display the entire table on the right if there's any matches we will pull in any matching rows from the left we are displaying all of our customers if any of these customers ever initiated a transaction we will include the data from those transactions we still have poppy puff in our table but she has no relevant transactions she never initiated one so yeah everybody those are joins a join is used to combine rows from two or more tables based on a related column between them such as a foreign key
like customer ID and that is a quick introduction to joins in MySQL
