When to use ON keyword in SQL Join

Question

When exactly can I use ON keyword while using joins in SQL be cause I'm confused.

Also, could any one please help me understand different types of joins with an example.

Thanks.

Answer 1

you'd want to do something like this:

select 
  tbl_items.item_name, 
  tbl_category.category_name
from
  tbl_items INNER JOIN tbl_categories
    ON tbl_items.category_id = tbl_categories.category_id

this would join your table named tbl_items to your table tbl_categories based on the primary key tbl_categories.category_id, which is linked to the foreign key tbl_items.category_id.

think of it as telling the db that you want to join two tables ON two matching fields.

different join types:

INNER JOIN returns only rows where the two fields compared in the ON statement match

LEFT OUTER JOIN (or just OUTER JOIN) returns all rows from the leftmost table in the join statement, and any rows from the rightmost table where the fields compared in the ON statement match.

RIGHT OUTER JOIN returns all rows from the rightmost table in the join statement and any rows from the leftmost table where the fields compared in the ON statement match.

Answer 2

ON is required to match fields on joined tables.

check the http://en.wikipedia.org/wiki/Join_%28SQL%29

Answer 3

Creating Joins with the ON Clause:

The ON Clause is used with Natural Join to specify the join condition, here is a more details written in form of bullet list:

• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specify arbitrary conditions or specify columns to join, the ON clause is used.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand

Example:

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
FROM employees e 
JOIN departments d
ON (e.department_id = d.department_id);

The Output:

EMPLOYEE_ID	LAST_NAME	DEPARTMENT_ID	DEPARTMENT_ID	LOCATION_ID
200	Whalen	10	10	1700
201	Hartstein	20	20	1800
202	Fay	20	20	1800
124	Mourgos	50	50	1500
141	Rajs	50	50	1500
142	Davies	50	50	1500
143	Matos	50	50	1500

---

Different Types of SQL Join:

Here is a good Visual Explanation of SQL Joins that would clarify the differences.

Thanks @mihai for this article.