//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: General SQL Server & Access Articles :: Could I get some help with JOINs?


Could I get some help with JOINs?

I see a lot of questions out there that suggest a lack of basic understanding of how tables in a relational database, well, relate to each other. Let's say we have the following schema of authors and the stories they have written: 
 
USE TempDB 
GO 
 
SET NOCOUNT ON 
 
CREATE TABLE dbo.Authors 

    AuthorID INT PRIMARY KEY, 
    FirstName VARCHAR(15) NOT NULL, 
    LastName VARCHAR(15) NOT NULL 

GO 
 
CREATE TABLE dbo.Stories 

    StoryID INT PRIMARY KEY, 
    AuthorID INT FOREIGN KEY 
        REFERENCES dbo.Authors, 
    dt SMALLDATETIME NOT NULL, 
    Title VARCHAR(32) NOT NULL 

GO 
 
INSERT dbo.Authors 
    SELECT 1, 'Aaron', 'Bertrand' 
    UNION SELECT 2, 'Jimmy', 'Hoffa' 
    UNION SELECT 3, 'Tommy', 'Hilfiger' 
    UNION SELECT 4, 'Brett', 'Hull' 
GO 
 
INSERT dbo.Stories 
    SELECT 100, 1, '20050227', 'SQL Server JOINs 101' 
    UNION SELECT 110, 2, '19740201', 'How to Hide Forever' 
    UNION SELECT 120, 4, '20050101', 'Slap Shot Fever' 
    UNION SELECT 130, 4, '20050219', 'What happened to hockey?' 
    UNION SELECT 140, 1, GETDATE(), 'How much can I type?' 
    UNION SELECT 150, NULL, '20041231', 'Anonymity Rules' 
GO
 
Notice that we have at least one author with no stories, and we also have at least one story with no author. 
 
First, let's handle the basic INNER JOIN. This returns every row from the "left" table with at least one matching row in the "right" table. (By "left" I mean the table mentioned on the left side of the JOIN expression.) 
 
SELECT  
        a.FirstName, a.LastName, s.Title, s.dt 
    FROM 
        Authors a 
    INNER JOIN 
        Stories s 
    ON 
        a.AuthorID = s.AuthorID 
    ORDER BY 
        a.LastName, a.FirstName
 
This returns all of the authors and each of their stories, one row per story. Notice that multiple authors have multiple rows in the result, and that there are no rows for any author without a story, or any story without an author. 
 
A common question is, "Should I use this ANSI-style JOIN, or should I use the old style with the JOIN criteria in the WHERE clause?" Here is the old-style syntax: 
 
SELECT 
        a.FirstName, a.LastName, s.Title, s.dt 
    FROM 
        Authors a, Stories s 
    WHERE 
        a.AuthorID = s.AuthorID 
    ORDER BY 
        a.LastName, a.FirstName
 
You will note that this produces the exact same result as the above query. Personally, I prefer the ANSI-style JOIN because it will last longer (the old style is deprecated) and because keeping the JOIN criteria separate from the filter criteria is much more intuitive and readable, IMHO. And while you should see no difference in the performance or in the results from INNER JOIN queries using either syntax, this is not always true for OUTER JOINs (I will provide examples later). So, since you should definitely use ANSI-style JOIN syntax for all OUTER JOINs, it would be consistent to use this syntax throughout all of your queries. 
 
Another common request I see for this kind of schema is, "How do I return one row per author, including the name of a story they wrote?" To which I always respond, "Which story do you want?" See, SQL Server doesn't know which story you want, so you have to be specific. There is no ANY() function in SQL Server, so you have to tell the engine whether you want the latest story they wrote, the first story title alphabetically, or something else that can be quantified — rather than expecting an arbitrary / non-deterministic result. And each of these results can be derived in a slightly different way. You must also account for cases where an author has not yet written any stories; do you include such authors in the result? If so, what do you want in the story column? 
 
This query list authors and their first title alphabetically: 
 
SELECT 
        a.FirstName, a.LastName, MIN(s.title) 
    FROM 
        dbo.Authors a 
    INNER JOIN 
        dbo.Stories s 
    ON 
        a.AuthorID = s.AuthorID 
    GROUP BY 
        a.firstName, a.LastName 
    ORDER BY 
        a.LastName, a.FirstName
 
This query lists authors and their latest story, including dt (this assumes that authorID + dt is unique). 
 
SELECT 
        a.FirstName, a.LastName, s.title, s.dt 
    FROM 
        dbo.Authors a 
    INNER JOIN  
    ( 
        SELECT 
            AuthorID, dt = MAX(dt) 
        FROM 
            dbo.Stories 
        GROUP BY  
            AuthorID 
    ) sub 
    ON 
        a.AuthorID = sub.AuthorID 
    INNER JOIN 
        dbo.Stories s 
    ON 
        s.AuthorID = sub.AuthorID 
        AND s.dt = sub.dt 
    ORDER BY 
        a.LastName, a.FirstName
 
What if you want to include authors that don't have stories? You'll need to replace the INNER JOINs with LEFT OUTER JOINs so that story-less authors aren't left out: 
 
SELECT 
        a.FirstName, a.LastName, s.title, s.dt 
    FROM 
        dbo.Authors a 
    LEFT OUTER JOIN  
    ( 
        SELECT 
            AuthorID, dt = MAX(dt) 
        FROM 
            dbo.Stories 
        GROUP BY 
            AuthorID 
    ) sub 
    ON 
        a.AuthorID = sub.AuthorID 
    LEFT OUTER JOIN 
        dbo.Stories s 
    ON 
        s.AuthorID = sub.AuthorID 
        AND s.dt = sub.dt 
    ORDER BY 
        a.LastName, a.FirstName
 
What if you want to return all stories, and their authors (if available)? 
 
SELECT 
        a.FirstName, a.LastName, s.title, s.dt 
    FROM 
        dbo.Authors a 
    RIGHT OUTER JOIN 
        dbo.Stories s 
    ON 
        a.AuthorID = s.AuthorID 
    ORDER BY 
        a.LastName, a.FirstName
 
Note that the only difference between a LEFT and RIGHT JOIN is the order of the tables that are being JOINed. Basically, the table on the mentioned side of the JOIN expression (e.g. the table mentioned first in a LEFT JOIN and last in a RIGHT JOIN) is the table that is going to produce real data for all rows, and the other table will be populated with NULLs when no matching row is found. 
 
The other types of JOINs are FULL OUTER and CROSS. 
 
FULL OUTER returns the every row from the left table *and* every row from the right table. If no match exists on one side, that side contains NULL values in all columns. To put it simply, a FULL OUTER JOIN is the UNION of a LEFT JOIN and RIGHT JOIN. 
 
SELECT 
        a.FirstName, a.LastName, s.title, s.dt 
    FROM 
        dbo.Authors a 
    FULL OUTER JOIN 
        dbo.Stories s 
    ON 
        a.AuthorID = s.AuthorID 
    ORDER BY 
        a.LastName, a.FirstName
 
A CROSS JOIN takes every row in the left table, and matches it with every row in the right table. A CROSS JOIN is generally referred to as the Cartesian product. 
 
SELECT 
        a.FirstName, a.LastName, s.title, s.dt 
    FROM 
        dbo.Authors a 
    CROSS JOIN 
        dbo.Stories s 
 
    -- note that no 'ON' clause is required 
 
    ORDER BY 
        a.LastName, a.FirstName
 
I don't know of many practical uses for this type of query, in terms of actually looking at the results, except a quick way to generate many menial rows from two tables and not trying to form any relationship between the two sides. 
 
I've probably left out quite a bit of information, but I'm tired. Please feel free to leave feedback about any errors above or about any JOIN topics you think should be included here. And don't forget to clean up: 
 
DROP TABLE dbo.Stories, dbo.Authors

Related Articles

Can I fix this mm/dd/yyyy <-> dd/mm/yyyy confusion once and for all?
How can I tell which version of MDAC I'm running?
How do I access MIN, MAX, SUM, COUNT values from SQL statements?
How do I change column order in a table structure?
How do I change the order of columns in a table?
How do I concatenate strings from a column into a single row?
How do I convert columns of values into a single list?
How do I determine if a database exists?
How do I document / compare my SQL Server database(s)?
How do I get the IDENTITY / AUTONUMBER value for the row I inserted?
How do I solve 'ADO Could Not Find The Specified Provider'?
Should I use BETWEEN in my database queries?
Why can't I use the * wildcard in a database search?
Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
Why do I get weird results when using both AND and OR in a query?
Why do some SQL strings have an 'N' prefix?
Why does AbsolutePosition return as -1?
Why doesn't SQL Server allow me to separate DATE and TIME?
Why is Query Analyzer only returning 255 characters?
Why should I avoid NULLs in my database?
How do I deal with an apostrophe (') in a SQL statement?

 

 


Created: 2/27/2005 | Last Updated: 4/19/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (234)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...