CODEWITHSUNDEEP
sql
user618677
user618677

Reputation: 5059

SQL INNER JOIN ON and USING

Say I have tables and I join them with inner join. What is the advantage of using "ON" over "USING". Or are they the same in all sense?

Upvotes: 16

Views: 19603

Answers (3)

ewok
ewok

Reputation: 21443

USING requires the names of the columns in both tables to be identical:

SELECT *
FROM   employee 
   INNER JOIN department 
      USING (DepartmentID);

whereas ON allows you to designate any columns to join on:

SELECT *
FROM   employee 
   JOIN department 
      ON employee.DepartmentID = department.ID;

In short, USING is more succinct, but ON is more flexible.

http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join

Upvotes: 17

onedaywhen
onedaywhen

Reputation: 57023

USING is an equijoin and causes duplicate columns to be removed from the resultset (arguably this makes it "more relational").

ON is a theta join (i.e. join condition need not be equality and may involve columns with differing names) and allows duplicate columns to appear in the resultset.

Upvotes: 17

HLGEM
HLGEM

Reputation: 96552

Not all flavors of SQL have USING so it is less portable as well as being a rare way to query.

Upvotes: 6

Related Questions