SELECT *
FROM heroes
LEFT JOIN appearances ON heroes.character_name=appearances.character_name
The results now show all the information that we had in our most recent table.
The advantage here is that we only need to update one line in our heroes table for each hero, so there is much less risk of error and we're taking less storage space. It's also quicker to process, but that's probably not noticeable for such a small database.
Let's break down the query.
SELECT * is the same as before. It says to show all fields. In this case, show all fields from all tables included in the query.
FROM heroes is the same as before, though I've left out the backticks. Backticks are not required in most cases.
The last line is new. LEFT JOIN means that we're connecting all rows from the second table that match the first table, and we're using the 'character_name' field of both tables to identify the match (heroes.character_name=appearances.character_name).
Notice that we identify the fields to use by naming the database first and separating them by a . . This ensures that the database doesn't get confused because the field names are the same in both tables.
Also notice that Scarlet Witch appears in the results because she's in the first table, even though she's not listed in the second table. The missing information is listed as NULL. Let's see what happens if we swap the tables around.
We could swap the table names in the query FROM appearances LEFT JOIN heroes ON heroes.character_name=appearances.character_name, but we can also simply change the type of join from LEFT to RIGHT.
SELECT *
FROM heroes
RIGHT JOIN appearances ON heroes.character_name=appearances.character_name
When you run this query, Scarlet Witch disappears because the second table (appearances) is our base and she doesn't appear in that table.
Let's go back to the original query so we can see Scarlet Witch.
SELECT *
FROM heroes
LEFT JOIN appearances ON heroes.character_name=appearances.character_name
Before we go on, notice that we've got unnecessary information in our results. 'id' is required by the database, but for now it has no value for us. Nor is there any value in repeating the 'character_name' data.
Let's grab only the fields we actually need.
SELECT character_name, hero_villain, first_appeared, power, movie
FROM heroes
LEFT JOIN appearances ON heroes.character_name=appearances.character_name
Notice that we've replaced * with the list of specific fields we want to include.
Unfortunately, when you run the query you'll see an error saying that the column 'character_name' is ambiguous. Although they're exactly the same in both tables, the database needs to be told which one to choose.
SELECT heroes.character_name, hero_villain, first_appeared, power, movie
FROM heroes
LEFT JOIN appearances ON heroes.character_name=appearances.character_name
Next: PHP Database Connections