SQL JOIN with USING

SQL JOIN with USING keyword.

  • The USING keyword specifies which columns to test for equality when two tables are joined.
  • USING keyword can be used instead of an ON keyword in the JOIN operations that have an explicit join clause.
  • USING keyword can be used with [INNER] JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN or FULL [OUTER] JOIN .
  • Not every RDBMS has implemented JOIN with the USING keyword.
  • A skeleton version of JOIN syntax including the USING keyword is:
    SELECT select_list
    FROM table_1 
    [[INNER] JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | FULL [OUTER] JOIN] table_2
    USING (column1,column2, ...)
    [WHERE search_conditions ]
    [ORDER BY column1, column2, ... ]
    Example 1 using RIGHT OUTER JOIN between the tables with USING keyword:
    SQL server does not support the USING keyword.
    MySQL, Oracle, PostgreSQL :
    SELECT Isbn_no, Author, Publisher, c.price, item_qty
     FROM order_books
     RIGHT OUTER  JOIN bookstore c
     USING (ISBN_NO)
     ORDER BY  c.price   
    For the both tries the result should be:
    isbn_no author publisher price item_qty
    1590593413 James L. Weaver, Kevin Mukhar and James Apress
    1861006314 Eric White Wrox 29 2
    1861006314 Eric White Wrox 29 1
    0471777781 Jeremy McPeak, Joe Fawcett Wrox 32 2
    0596009747 Sal Mangano OReilly 32
    0764579088 Nicholas C. Zakas Wrox 35 4
    1861002025 Charles Williams Wrox 38 5
    1861002025 Charles Williams Wrox 38 5
    0201703092 Judith S. Bowman Addison Wesley 39 2
    0201703092 Judith S. Bowman Addison Wesley 39 10
    0764557599 Simon Robinson and Jay Glynn Wrox 42 1
    0764557599 Simon Robinson and Jay Glynn Wrox 42 2
    0672325764 Steven Holzner Sams Publishing 49 1
    Example 2 using LEFT OUTER JOIN between the tables with USING keyword:
    SQL server does not support the USING keyword.
    MySQL, Oracle, PostgreSQL :
    SELECT Isbn_no, Author, Publisher, c.price, item_qty
     FROM order_books
     LEFT OUTER  JOIN bookstore c
     USING (ISBN_NO)
     ORDER BY  c.price   
    For the both tries the result should be:
    isbn_no author publisher price item_qty
    1861006314 Eric White Wrox 29 1
    1861006314 Eric White Wrox 29 2
    0471777781 Jeremy McPeak, Joe Fawcett Wrox 32 2
    0764579088 Nicholas C. Zakas Wrox 35 4
    1861002025 Charles Williams Wrox 38 5
    1861002025 Charles Williams Wrox 38 5
    0201703092 Judith S. Bowman Addison Wesley 39 2
    0201703092 Judith S. Bowman Addison Wesley 39 10
    0764557599 Simon Robinson and Jay Glynn Wrox 42 1
    0764557599 Simon Robinson and Jay Glynn Wrox 42 2
    0672325764 Steven Holzner Sams Publishing 49 1
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.