Page 121 - SQL

Implicit Join

Joins can also be performed by having several tables in the from clause, separated with commas ,
and defining the relationship between them in the where clause. This technique is called an Implicit
Join (since it doesn't actually contain a join clause).

All RDBMSs support it, but the syntax is usually advised against. The reasons why it is a bad idea
to use this syntax are:

• It is possible to get accidental cross joins which then return incorrect results, especially if you
have a lot of joins in the query.
• If you intended a cross join, then it is not clear from the syntax (write out CROSS JOIN
instead), and someone is likely to change it during maintenance.

The following example will select employee's first names and the name of the departments they
work for:

SELECT e.FName, d.Name
FROM Employee e, Departments d
WHERE e.DeptartmentId = d.Id

This would return the following from the example database:

e.FName d.Name

James HR

John HR

Richard Sales

Left Outer Join

A Left Outer Join (also known as a Left Join or Outer Join) is a Join that ensures all rows from the
left table are represented; if no matching row from the right table exists, its corresponding fields
are NULL.

The following example will select all departments and the first name of employees that work in that
department. Departments with no employees are still returned in the results, but will have NULL
for the employee name:

SELECT Departments.Name, Employees.FName
FROM Departments
LEFT OUTER JOIN Employees
ON Departments.Id = Employees.DepartmentId

This would return the following from the example database:

https://riptutorial.com/ 103