Combine Information from Multiple MySQL Tables with JOIN
You can use a JOIN SELECT query to combine information from more than one MySQL table. With JOIN, the tables are combined side by side, and the information is retrieved from both tables.
Tables are combined by matching data in a column — the column that they have in common. The combined results table produced by a join contains all the columns from both tables. For instance, if table1 has two columns (memberID and height), and table2 has two columns (memberID and weight), a join results in a table with four columns: memberID (from table1), height, memberID (from table2), and weight.
The two common types of joins are an inner join and an outer join. The difference between an inner and outer join is in the number of rows included in the results table.
Inner join: The results table produced by an inner join contains only rows that existed in both tables.
Outer join: The combined table produced by an outer join contains all rows that existed in one table with blanks in the columns for the rows that did not exist in the second table.
For instance, if table1 contains a row for Joe and a row for Sally, and table2 contains only a row for Sally, an inner join would contain only one row: the row for Sally. However, an outer join would contain two rows — a row for Joe and a row for Sally — even though the row for Joe would have a blank field for weight.
The results table for the outer join contains all the rows for one table. If any of the rows for that table don’t exist in the second table, the columns for the second table are empty. Clearly, the contents of the results table are determined by which table contributes all its rows, requiring the second table to match it.
Two kinds of outer joins control which table sets the rows and which must match: a LEFT JOIN and a RIGHT JOIN.
You use different SELECT queries for an inner join and the two types of outer joins. The following query is an inner join:
SELECT columnnamelist FROM table1,table2 WHERE table1.col2 = table2.col2
And these queries are outer joins:
SELECT columnnamelist FROM table1 LEFT JOIN table2 ON table1.col1=table2.col2 SELECT columnnamelist FROM table1 RIGHT JOIN table2 ON table1.col1=table2.col2
In all three queries, table1 and table2 are the tables to be joined. You can join more than two tables. In both queries, col1 and col2 are the names of the columns being matched to join the tables. The tables are matched based on the data in these columns. These two columns can have the same name or different names, but they must contain the same type of data.
As an example of inner and outer joins, consider a Clothes catalog with two tables. One table is Product, with the two columns Name and Type holding the following data:
Name Type T-shirt Shirt Dress shirt Shirt Jeans Pants
The second table is Color, with two columns Name and Color holding the following data:
Name Color T-shirt white T-shirt red Loafer black
You need to ask a question that requires information from both tables. If you do an inner join with the following query:
SELECT * FROM Product,Color WHERE Product.Name = Color.Name
you get the following results table with four columns: Name (from Product), Type, Name (from Color), and Color.
Name Type Name Color T-shirt Shirt T-shirt white T-shirt Shirt T-shirt red
Notice that only T-shirt appears in the results table — because only T-shirt was in both of the original tables, before the join. On the other hand, suppose you do a left outer join with the following query:
SELECT * FROM Product LEFT JOIN Color ON Product. Name=Color. Name
You get the following results table, with the same four columns — Name (from Product), Type, Name (from Color), and Color — but with different rows:
Name Type Name Color T-shirt Shirt T-shirt white T-shirt Shirt T-shirt red Dress shirt Shirt <NULL> <NULL> Jeans Pants <NULL> <NULL>
This table has four rows. It has the same first two rows as the inner join, but it has two additional rows — rows that are in the Product table on the left but not in the Color table. Notice that the columns from the table Color are blank for the last two rows.
And, on the third hand, suppose that you do a right outer join with the following query:
SELECT * FROM Product RIGHT JOIN Color ON Product.petName=Color. Name
You get the following results table, with the same four columns, but with still different rows:
petName petType petName petColor T-shirt Shirt T-shirt white T-shirt Shirt T-shirt red <NULL> <NULL> Loafers Black
Notice that these results contain all the rows for the Color table on the right but not for the Product table. Notice the blanks in the columns for the Product table, which doesn’t have a row for Loafers.
Sometimes it’s useful to find out which rows in a table have no matching entries in another table. For example, suppose that you want to know who has never logged in to your Members Only section. Suppose you have one table with the member’s login name (Member) and another table with the login dates (Login).
You can ask this question by selecting from the two tables. You can find out which login names don’t have an entry in the Login table with the following query:
SELECT loginName FROM Member LEFT JOIN Login ON Member.loginName=Login.loginName WHERE Login.loginName IS NULL
This query gives you a list of all the login names in the Member table that aren’t in the Login table.