메뉴 닫기

SQL JOIN

  • A SQL JOIN combines records from two tables.
  • A JOIN locates related column values in the two tables.
  • A query can contain zero, one, or multiple JOIN operations.
  • INNER JOIN is the same as JOIN; the keyword INNER is optional.

The definitive guide
for data professionals
Order today! 
  See 2 min video

 


 

Different types of JOINs

  • (INNER) JOIN: Select records that have matching values in both tables.
  • LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
  • RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
  • FULL (OUTER) JOIN: Selects all records that match either left or right table records.

All INNER and OUTER keywords are optional.
Details about the differences between these JOINs are available in subsequent tutorial pages.


The SQL JOIN syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename1 JOIN tablename2
  3. ON columnname1 = columnname2
  4. WHERE condition

The general syntax with INNER is:

  1. SELECT columnnames
  2. FROM tablename1 INNER JOIN tablename2
  3. ON columnname1 = columnname2
  4. WHERE condition

Note: The INNER keyword is optional: it is the default as well as the most commmonly used JOIN operation.


SQL JOIN Examples

Problem: List all orders with customer information

  1. SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
  2. FROM [Order] JOIN Customer
  3. ON [Order].CustomerId = Customer.Id

In this example using table aliases for [Order] and Customer might have been useful.
Results: 830 records.

OrderNumber TotalAmount FirstName LastName City Country
542378 440.00 Paul Henriot Reims France
542379 1863.40 Karin Josephs Münster Germany
542380 1813.00 Mario Pontes Rio de Janeiro Brazil
542381 670.80 Mary Saveley Lyon France
542382 3730.00 Pascale Cartrain Charleroi Belgium
542383 1444.80 Mario Pontes Rio de Janeiro Brazil
542384 625.20 Yang Wang Bern Switzerland

Problem: List all orders with
product names, quantities, and prices

  1. SELECT O.OrderNumber, CONVERT(date,O.OrderDate) AS Date,
  2. P.ProductName, I.Quantity, I.UnitPrice
  3. FROM [Order] O
  4. JOIN OrderItem I ON O.Id = I.OrderId
  5. JOIN Product P ON P.Id = I.ProductId
  6. ORDER BY O.OrderNumber

This query performs two JOIN operations with 3 tables.
The O, I, and P are table aliases. Date is a column alias.
Results: 2155 records

OrderNumber Date ProductName Quantity UnitPrice
542378 7/4/2012 12:00:00 AM Queso Cabrales 12 14.00
542378 7/4/2012 12:00:00 AM Singaporean Hokkien Fried Mee 10 9.80
542378 7/4/2012 12:00:00 AM Mozzarella di Giovanni 5 34.80
542379 7/5/2012 12:00:00 AM Tofu 9 18.60
542379 7/5/2012 12:00:00 AM Manjimup Dried Apples 40 42.40
542380 7/8/2012 12:00:00 AM Jack’s New England Clam Chowder 10 7.70
542380 7/8/2012 12:00:00 AM Manjimup Dried Apples 35 42.40
542380 7/8/2012 12:00:00 AM Louisiana Fiery Hot Pepper Sauce 15 16.80
542381 7/8/2012 12:00:00 AM Gustaf’s Knäckebröd 6 16.80
542381 7/8/2012 12:00:00 AM Ravioli Angelo 15 15.60
542381 7/8/2012 12:00:00 AM Louisiana Fiery Hot Pepper Sauce 20 16.80
542382 7/9/2012 12:00:00 AM Sir Rodney’s Marmalade 40 64.80
542382 7/9/2012 12:00:00 AM Geitost 25 2.00

9 Comments

  1. g then

    Hi there! I know this is somewhat off topic but I was
    wondering if you knew where I could get a captcha plugin for my
    comment form? I’m using the same blog platform as yours and I’m
    having difficulty finding one? Thanks a lot!

  2. https://tinyurl.com/rsacwgxy g

    Hi I am so happy I found your web site, I really
    found you by accident, while I was researching on Google for
    something else, Nonetheless I am here now and would just like to say kudos for a marvelous post and a all
    round exciting blog (I also love the theme/design), I don’t have time
    to go through it all at the minute but I have book-marked it and also included your RSS feeds,
    so when I have time I will be back to read a lot more,
    Please do keep up the fantastic jo.

  3. Hipolito Bobson

    I just want to tell you that I’m very new to weblog and honestly savored this blog. Most likely I’m want to bookmark your website . You really come with beneficial stories. Cheers for sharing your web site.

  4. best web hosting 2020

    Howdy! Someone in my Facebook group shared this site with us
    so I came to look it over. I’m definitely enjoying the information. I’m book-marking and will be tweeting this to my followers!
    Fantastic blog and outstanding style and
    design.

  5. best web hosting 2020

    Definitely believe that which you stated. Your favorite reason appeared to be on the web the easiest thing to be aware of.
    I say to you, I certainly get irked while people consider worries that they just do not know
    about. You managed to hit the nail upon the top and also defined out
    the whole thing without having side-effects , people could take a signal.
    Will likely be back to get more. Thanks

댓글 남기기

이메일은 공개되지 않습니다.