MySQL加入以在WHERE语句中包含不匹配的记录

Question

I need to output data from two table if it exists.

I want to select all records in Table 1 for display in a PHP Recordset using a dynamic html table. I also need to display some data from Table 2 if it exists. My existing query is not selecting all the data I need from the two tables.

Table 1

EmpNum     FirstName   EmpType
541        Robert      88
222        Samuel      88
521        Anthony     88

Table 2

ID   SecretKey     NickName
541  6565          Bob
222  9999          Sam

I also want to "join" this data with another table that may or may not have matches. This is throwing off my results.

SELECT Table1.EmpNum, Table1.FirstName, Table2.ID, Table2.SecretKey, Table2.NickName FROM Table1, Table2 WHERE Table1.EmpNum=Table2.ID AND Table1.EmpType = '88'

This query is omitting the EmpNum 521 from the tabular data because my WHERE clause does not find a match.

I'd like to have all the records display from Table 1 and then append data from Table 2 if a match exists.

Example DESIRED output:

EmpNum     FirstName   EmpType     SecretKey     NickName
541        Robert      88          6565          Bob
222        Samuel      88          9999          Sam
521        Anthony     88

In this example above Anthony is still displayed even though he has not records in Table 2.

How do I alter my join to achieve this.

Answer 1

Use an outer join -

SELECT Table1.EmpNum, Table1.FirstName, Table2.ID, Table2.SecretKey, Table2.NickName 
FROM Table1
LEFT OUTER JOIN Table2 
ON Table1.EmpNum = Table2.ID
WHERE Table1.EmpType = '88'

You can continue to outer join additional tables as you need, getting all of the data from the first table and then only matching data from the subsequent tables.