Thread: ques

Pls any one explain the difference between left join & left outer join with example??

Left join and left outer join are one and the same.The use of word "outer" is optional.

A left outer join will select all records from the first table, and any records in the second table that match the joined keys.When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Example

SELECT *
FROM user u
LEFT OUTER JOIN telephone t ON t.user_id = u.id

The above example will return a list of all users, plus any telephone records if they are available (if they aren't available, you'll get NULL for the telephone values).

The keyword OUTER is optional, but in my opinion it should be mandatory. Until such time as it is, my advice is always to write it, to remind yourself that it's an outer join.

The keyword LEFT, RIGHT, or FULL is mandatory. Left, right, and full outer joins are the only types of outer join. And of course the keyword JOIN is mandatory.

You mentioned INNER JOIN. This is not the same as FULL OUTER JOIN. INNER means all result rows are rows that were produced by match some condition between the two tables. An outer join has result rows where sometimes there isn't a match, yet rows from one table, or the other, or both, are returned without a match.

There is one other type of join besides inner and outer, and that's the CROSS JOIN, but that's a different question for another day.