When to use a left outer join?

by

Joins are used to combine two related tables together.

In your example, you can combine the Employee table and the Department table, like so:

SELECT FNAME, LNAME, DNAME
FROM
EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.DNO=DEPARTMENT.DNUMBER

This would result in a recordset like:

FNAME   LNAME   DNAME
-----   -----   -----
John    Smith   Research
John    Doe     Administration

I used an INNER JOIN above. INNER JOINs combine two tables so that only records with matches in both tables are displayed, and they are joined in this case, on the department number (field DNO in Employee, DNUMBER in Department table).

LEFT JOINs allow you to combine two tables when you have records in the first table but might not have records in the second table. For example, let’s say you want a list of all the employees, plus any dependents:

SELECT EMPLOYEE.FNAME as employee_first, EMPLOYEE.LNAME as employee_last, DEPENDENT.FNAME as dependent_last, DEPENDENT.LNAME as dependent_last
FROM
EMPLOYEE INNER JOIN DEPENDENT ON EMPLOYEE.SSN=DEPENDENT.ESSN

The problem here is that if an employee doesn’t have a dependent, then their record won’t show up at all — because there’s no matching record in the DEPENDENT table.

So, you use a left join which keeps all the data on the “left” (i.e. the first table) and pulls in any matching data on the “right” (the second table):

SELECT EMPLOYEE.FNAME as employee_first, EMPLOYEE.LNAME as employee_last, DEPENDENT.FNAME as dependent_first, DEPENDENT.LNAME as dependent_last
FROM
EMPLOYEE LEFT JOIN DEPENDENT ON EMPLOYEE.SSN=DEPENDENT.ESSN

Now we get all of the employee records. If there is no matching dependent(s) for a given employee, the dependent_first and dependent_last fields will be null.

More Related Contents:

  1. What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? [duplicate]
  2. 1052: Column ‘id’ in field list is ambiguous
  3. Join tables with SUM issue in MYSQL
  4. Join between tables in two different databases?
  5. Subqueries vs joins
  6. Required to join 2 tables with their FKs in a 3rd table
  7. How can I join multiple SQL tables using the IDs?
  8. Why does MySQL report a syntax error on FULL OUTER JOIN?
  9. Mixing ANSI 1992 JOINs and COMMAs in a query
  10. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  11. MySQL – Conditional Foreign Key Constraints
  12. Cumulative sum over a set of rows in mysql
  13. Entity-Attribute-Value Table Design
  14. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  15. MySQL JOIN the most recent row only?
  16. Remove Primary Key in MySQL
  17. How to find non-existing data from another Table by JOIN?
  18. MySQL Inner Join Query Multiple Tables
  19. Difference between these two joining table approaches?
  20. Laravel join with 3 Tables
  21. Difference between two table structure
  22. MySQL LEFT JOIN 3 tables
  23. Strings as Primary Keys in MYSQL Database [closed]
  24. how to use a like with a join in sql?
  25. In what order are MySQL JOINs evaluated?
  26. How to store a one to many relation in MySQL database?
  27. MySQL Multiple Joins in one query?
  28. How to declare a variable in MySQL?
  29. Proper database model for a user feedback system (an interesting case)
  30. MySQL – Replace Character in Columns

Leave a Comment