SQL | ON Clause

The join condition for the natural join is basically an EQUIJOIN of all columns with same name. To specify arbitrary conditions or specify columns to join, the ON Clause is used.

  1. The join condition is separated from other search conditions.
  2. The ON Clause makes code easy to understand.
  3. ON Clause can be used to join columns that have different names.
  4. We use ON clause to specify a join condition. This lets us specify join conditions separate from any search or filter conditions in the WHERE clause.

EXAMPLES:

We will apply the below mentioned commands on the following base tables:

    Employees Table

    Departments Table

    QUERY 1: Write SQL query to find the working location of the employees. Also give their respective employee_id, last_name and department_id?

    
    Input :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);
    Output : 
    

Explanation: The example shown joins the DEPARTMENT_ID column in the EMPLOYEES and DEPARTMENTS
tables using ON Clause, and thus shows the required data.

We will apply the below mentioned commands on the following base tables:

    Countries Table

    Locations Table

QUERY 2: Write SQL query to find the location_id, street_address, postal_code and their respective country name?


Input : SELECT l.location_id, l.street_address, l.postal_code, c.country_name
FROM locations l JOIN countries c
ON (l.country_id = c.country_id);
Output : 

Explanation: The example shown joins the COUNTRY_ID column in the LOCATIONS and COUNTRIES
tables using the ON Clause, and thus shows the required details.



My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.