. SQL . SQL Join

SQL Join

SQL Join Example One - A "Natural" Join

Q: What Movies was Judy Garland in, and what year were they released? 
   
 Select Thespian_Film_Table.Film_Title, Film_Year
   From Thespian_Film_Table,
        Film_Table
  Where Thespian_Last_Name equals "Garland"
    and Thespian_First_Name equals "Judy"
    and Thespian_Film_Table.Film_Title = Film_Table.Film_Title;

   
 Andy Hard Meets Debutante,          1940
 Babes in Arms,                      1939
 Babes on Broadway,                  1941
 A Child is Waiting,                 1963
 Clock - The,                        1945
 Easter Parade,                      1948
 Everybody Sing,                     1938
 For Me and my Gal,                  1942
 Girl Crazy,                         1943
 Harvey Girls - The,                 1946
 I Could Go on Singing,              1963
 In the Good Old Summertime,         1949
 Judgement at Nuremberg,             1961
 Life Begins for Andy Hardy,         1941
 Listen - Darling,                   1938
 Little Nelly Kelly,                 1940
 Love Finds Andy Hardy,              1938
 Meet Me in St. Louis,               1944
 Pirate - The,                       1948
 Presenting Lily Mars,               1943
 Star is Born - A,                   1954
 Strictly G.I.,                      1944
 Strike Up the Band,                 1940
 Thoroughbreads Don't Cry,           1937
 Thousands Cheer,                    1943
 Till the Clouds Roll By,            1946
 Wizard of Oz - The,                 1939
 Ziegfield Follies,                  1946
 Ziegfeld Girl,                      1941
Note: We use the column Film_Title to link the tables together. This works because the values will be exactly the same in both tables for those fields

SQL Join Example Two - A Subselect

Q: Same question, but use a Subquery. 
   
 Select Film_Title, Film_Year
   From Film_Table
  Where Film_Title in
        (Select Film_Title
           From Thespian_Film_Table
          Where Thespian_Last_Name equals "Garland"
            and Thespian_First_Name equals "Judy");

   
 Andy Hard Meets Debutante,          1940
 Babes in Arms,                      1939
 Babes on Broadway,                  1941
 A Child is Waiting,                 1963
 Clock - The,                        1945
 Easter Parade,                      1948
 Everybody Sing,                     1938
 For Me and my Gal,                  1942
 Girl Crazy,                         1943
 Harvey Girls - The,                 1946
 I Could Go on Singing,              1963
 In the Good Old Summertime,         1949
 Judgement at Nuremberg,             1961
 Life Begins for Andy Hardy,         1941
 Listen - Darling,                   1938
 Little Nelly Kelly,                 1940
 Love Finds Andy Hardy,              1938
 Meet Me in St. Louis,               1944
 Pirate - The,                       1948
 Presenting Lily Mars,               1943
 Star is Born - A,                   1954
 Strictly G.I.,                      1944
 Strike Up the Band,                 1940
 Thoroughbreads Don't Cry,           1937
 Thousands Cheer,                    1943
 Till the Clouds Roll By,            1946
 Wizard of Oz - The,                 1939
 Ziegfield Follies,                  1946
 Ziegfeld Girl,                      1941
Note: We use the column Film_Title to link the tables together. This works because the values will be exactly the same in both tables for those fields.
Also Note: Both methods of joining tables should give the same results. The natural join is more CPU efficent on most SQL platforms.
One More Note: You can see the syntax for subselect on our subselect page.
Comments Comments are left by visitors to FluffyCat.com, are not endorsed by FluffyCat.com, and may or may not be accurate.
Comment by TheMadProfessor Rate this Comment

Another way now available to do joins is with the JOIN keyword. For example, the above select could have been written as:

Select Thespian_Film_Table.Film_Title, Film_Year
From Thespian_Film_Table
Inner Join Film_Table
On Thespian_Film_Table.Film_Title = Film_Table.Film_Title
Where Thespian_Last_Name equals "Garland"
and Thespian_First_Name equals "Judy";

This appears to be no better that the above syntax other than emphasizing the field(s) that connect the tables together. The problem with an inner join is that, if there are no matching rows for a given value in one table, no rows appear in the result table. However, with the JOIN phrase, one can now also do what are known as left and right joins. These cause rows to appear in the result table with data from one table even if there is no matching row in the other. (Whether it is a LEFT join or RIGHT join depends which table's data you want to appear - if the first, it's left; if the second, it's right.)

For example, if you have a table of customers and another of orders linked by cust_no and you want a list of orders by Florida customers for 2005:

SELECT c.cust_no, o.order_no
FROM customers c, orders.o
WHERE c.cust_no = o.cust_no
AND c.cust_state = "FL"
AND o.order_year = 2005;

all is well and good. However, if you wanted a list of all Florida customers PLUS any orders they made in 2005, the above wouldn't work since any Florida customers that did not order in 2005 wouldn't be listed. Instead, you can do:

SELECT c.cust_no, o.order_no
FROM customers c
LEFT JOIN orders.o
ON c.cust_no = o.cust_no
WHERE c.cust_state = "FL"
AND o.order_year = 2005;

Now you will see all of your Florida customers, regardless of whether they ordered in 2005 or not. If they did, you would also see their orders listed.

Sign In
to add your own comment