SQL Server FAQs - Understanding SELECT Statements with Joins and Subqueries--tutorial.jcwcn.com

answers are provided with tutorial exercises on joining multiple tables with inner and outer joins; using subqueries with IN, EXISTS, and FROM clauses; using UNION operations. Topics included in this FAQ are:

How To Join Two Tables in a Single Query?
How To Write a Query with an Inner Join?
How To Define and Use Table Alias Names?
How To Write a Query with a Left Outer Join?
How To Write a Query with a Right Outer Join?
How To Write a Query with a Full Outer Join?
How To Write an Inner Join with the WHERE Clause?
How To Name Query Output Columns?
What Is a Subquery in a SELECT Query Statement?
How To Use Subqueries with the IN Operators?
How To Use Subqueries with the EXISTS Operators?
How To Use Subqueries in the FROM Clause?
How To Count Groups Returned with the GROUP BY Clause?
How To Return the Top 5 Rows from a SELECT Query?
How To Return the Second 5 Rows?
How To Use UNION to Merge Outputs from Two Queries Together?
How To Use ORDER BY with UNION Operators

Please note that all answers and tutorials are based on MS SQL Server 2005. To follow tutorials in this collection, you should connect to SQL server as a regular user who has enough permissions to create table and insert date in "FyiCenterData". It is assumed that you are using the temporary database, FyiCenterData, created in previous tutorial collections. Some sample scripts require database tables created by other samples in the beginning of the collection.

For questions on SELECT statement basics and GROUP BY, see the previous FAQ collection.

Two tables can be joined together in a query in 4 ways:

Inner Join: Returns only rows from both tables that satisfy the join condition.
Left Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the first (left) table.
Right Outer Join: Returns rows from both tables that satisfy the join condition, and the rest of rows from the second (right) table.
Full Outer Join: Returns rows from both tables that satisfy the join condition, the rest of rows from the first (left) table, and the rest of rows from the second (right) table.

If you want to query from two tables with an inner join, you can use the INNER JOIN ... ON clause in the FROM clause. The tutorial exercise below creates another testing table and returns output with an inner join from two tables: fyi_links and fyi.rates. The join condition is that the id in the fyi_links table equals to the id in the fyi_rates table:

CREATE TABLE fyi_rates (id INTEGER, 
   comment VARCHAR(16))
GO

INSERT INTO fyi_rates VALUES (101, 'The best')
GO
INSERT INTO fyi_rates VALUES (102, 'Well done')
GO
INSERT INTO fyi_rates VALUES (103, 'Thumbs up')
GO
INSERT INTO fyi_rates VALUES (204, 'Number 1')
GO
INSERT INTO fyi_rates VALUES (205, 'Not bad')
GO
INSERT INTO fyi_rates VALUES (206, 'Good job')
GO
INSERT INTO fyi_rates VALUES (207, 'Nice tool')
GO

SELECT fyi_links.id, fyi_links.url, 
   fyi_rates.comment FROM fyi_links
   INNER JOIN fyi_rates ON fyi_links.id = fyi_rates.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up

Note that when multiple tables are used in a query, column names need to be prefixed with table names in case the same column name is used in both tables.

Note also that there are a number of rows from both tables did not return in the output because they did meet the join condition.

When column names need to be prefixed with table names, you can define table alias name and use them to prefix column names. To define an alias for a table name, just enter the alias name right after the original table name in the FROM clause as shown in the following select statement:

SELECT l.id, l.url, r.comment FROM fyi_links l 
   INNER JOIN fyi_rates r ON l.id = r.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up

If you want to query from two tables with a left outer join, you can use the LEFT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a left outer join from two tables: fyi_links and fyi_rates. The join condition is that the id in the fyi_links table equals to the id in the fyi_rates table:

SELECT l.id, l.url, r.comment FROM fyi_links l 
   LEFT OUTER JOIN fyi_rates r ON l.id = r.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up
104     www.mysql.com           NULL
105     www.oracle.com          NULL
106     www.php.net             NULL
107     www.winrunner.com       NULL

Note that a left outer join may return extra rows from the first (left) table that do not satisfy the join condition. In those extra rows, columns from the second (right) table will be given null values.

The extra rows returned from the left outer join in this example represents links that have no rates in the above example.

If you want to query from two tables with a right outer join, you can use the RIGHT OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a right outer join from two tables: fyi_links and fyi_rates. The join condition is that the id in the fyi_links table equals to the id in the fyi_rates table:
SELECT l.id, l.url, r.comment FROM fyi_links l 
   RIGHT OUTER JOIN fyi_rates r ON l.id = r.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up
NULL    NULL                    Number 1
NULL    NULL                    Not bad
NULL    NULL                    Good job
NULL    NULL                    Nice tool
Note that a right outer join may return extra rows from the second (right) table that do not satisfy the join condition. In those extra rows, columns from the first (left) table will be given null values.
The extra rows returned from the right outer join in this example represents rates that have no links in the above example.
If you want to query from two tables with a full outer join, you can use the FULL OUTER JOIN ... ON clause in the FROM clause. The following query returns output with a full outer join from two tables: fyi_links and fyi_rates. The join condition is that the id in the fyi_links table equals to the id in the fyi_rates table:
SELECT l.id, l.url, r.comment FROM fyi_links l 
   FULL OUTER JOIN fyi_rates r ON l.id = r.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up
104     www.mysql.com           NULL
105     www.oracle.com          NULL
106     www.php.net             NULL
107     www.winrunner.com       NULL
NULL    NULL                    Number 1
NULL    NULL                    Not bad
NULL    NULL                    Good job
NULL    NULL                    Nice tool
As you can see, an full outer join returns 3 groups of rows: 
The rows from both tables that satisfy the join condition. 
The rows from the first (left) table that do not satisfy the join condition. 
The rows from the second (right) table that do not satisfy the join condition. 
If you don't want to use the INNER JOIN ... ON clause to write an inner join, you can put the join condition in the WHERE clause as shown in the following query example:
SELECT l.id, l.url, r.comment 
   FROM fyi_links l, fyi_rates r WHERE l.id = r.id
GO
id      url                     comment
101     dev.fyicenter.com       The best
102     dba.fyicenter.com       Well done
103     sqa.fyicenter.com       Thumbs up
Each column in the query output has a default name. If you don't like the default name, you can specify a new name for any column in the query output by using the AS clause. The following statement shows you a good example:
SELECT tag AS Category, YEAR(created) AS Year, 
   COUNT(*) AS Counts FROM fyi_links 
   GROUP BY tag, YEAR(created) ORDER BY COUNT(*) DESC
GO
Category   Year   Counts
SQA        2003   1
DEV        2004   1
DBA        2005   1
DBA        2006   1
DEV        2006   1
DBA        2007   1
SQA        2007   1
A subquery is a SELECT statement used as part of the selection criteria of the main SELECT statement. The subquery specified in the WHERE clause will be evaluated repeated on each row of the selection base table. The output of the subquery will be used in the final evaluation of the criteria. Usually, subqueries are used in the following Boolean operations:
"expression IN (subquery)" - True if the expression matches one of the returned values from the subquery. 
"expression NOT IN (subquery)" - True if the expression does not match any of the returned values from the subquery. 
"EXISTS (subquery)" - True if the subquery returns one or more rows. 
"NOT EXISTS (subquery)" - True if the subquery returns no rows. 
A subquery can be used with the IN operator as "expression IN (subquery)". The subquery should return a single column with one or more rows to form a list of values to be used by the IN operation. The following tutorial exercise shows you how to use a subquery with the IN operator. It returns all links with ids in the fyi_rates table.
SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links WHERE id IN (SELECT id FROM fyi_rates)
GO
id      url                     tag     Year
101     dev.fyicenter.com       DEV     2006
102     dba.fyicenter.com       DBA     2007
103     sqa.fyicenter.com       SQA     2007

SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links 
   WHERE id IN (101, 102, 103, 204, 205, 206, 207)
GO
id      url                     tag     Year
101     dev.fyicenter.com       DEV     2006
102     dba.fyicenter.com       DBA     2007
103     sqa.fyicenter.com       SQA     2007
As you can see, the subquery is equivalent to a list of values.
A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. The following statement is a good example of "EXISTS (subquery)". It returns rows from fyi_links table that there are rows existing in the fyi_rates table with the same id.
SELECT id, url, tag, YEAR(created) As year 
   FROM fyi_links WHERE EXISTS (
   SELECT * FROM fyi_rates 
   WHERE fyi_rates.id = fyi_links.id)
GO
id      url                     tag     Year
101     dev.fyicenter.com       DEV     2006
102     dba.fyicenter.com       DBA     2007
103     sqa.fyicenter.com       SQA     2007
Note that the subquery uses columns from the source table of the outer query. 
If you have a query returning many rows of data, and you want to perform another query on those rows, you can put the first query as a subquery in the FROM clause of the second query. A subquery used in this way become a temporary table, and you must provide a table alias name for the subquery as in "SELECT ... FROM (SELECT ...) aliasName". The following statement shows you how to use a subquery as base table for the main query:
SELECT * FROM (SELECT l.id, l.url, r.comment
   FROM fyi_links l LEFT OUTER JOIN fyi_rates r 
   ON l.id = r.id) WHERE url LIKE '%er%'
GO
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.

SELECT * FROM (SELECT l.id, l.url, r.comment
   FROM fyi_links l LEFT OUTER JOIN fyi_rates r 
   ON l.id = r.id) s WHERE s.url LIKE '%er%'
GO
101	dev.fyicenter.com	The best
102	dba.fyicenter.com	Well done
103	sqa.fyicenter.com	Thumbs up
107	www.winrunner.com	NULL
The error on the first query is caused by the missing alias name to name output of the subquery as a temporary table. 
If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. If you want to count the number of groups, you can put the GROUP BY query into a subquery and apply the COUNT(*) function on the main query as shown in the following tutorial exercise:
SELECT tag AS Category, YEAR(created) AS Year, 
   COUNT(*) AS Counts FROM fyi_links GROUP BY tag, 
   YEAR(created)
GO
Category   Year   Counts
SQA        2003   1
DEV        2004   1
DBA        2005   1
DBA        2006   1
DEV        2006   1
DBA        2007   1
SQA        2007   1

SELECT COUNT(*) FROM (
   SELECT tag AS Category, YEAR(created) AS Year, 
   COUNT(*) AS Counts FROM fyi_links GROUP BY tag, 
   YEAR(created) ) groups
GO
7
If you want the query to return only the first 5 rows, you can use the "TOP 5" clause. The TOP clause takes one parameter to indicate how many top rows to return. The following statements returns the first 5 rows and 3 rows from the fyi_links:
SELECT TOP 5 id, url, counts, tag FROM fyi_links 
   ORDER BY counts DESC
GO
id      url                     counts  tag
102     dba.fyicenter.com       972     DBA
105     www.oracle.com          960     DBA
107     www.winrunner.com       828     SQA
103     sqa.fyicenter.com       728     SQA
106     www.php.net             439     DEV

SELECT TOP 3 id, url, counts, tag FROM fyi_links 
   ORDER BY counts DESC
GO
id      url                     counts  tag
102     dba.fyicenter.com       972     DBA
105     www.oracle.com          960     DBA
107     www.winrunner.com       828     SQA
If you want to display query output in multiple pages with 5 rows per page, and the visitor wants to see the output for the second page, you need to display query output from row 6 to row 10. If you are using MySQL server, you can use the "LIMIT startRow maxRows". 
But the LIMIT clause is not supported by the SQL server. And there seems to be no easy workaround. You may consider to return the top 10 rows, skip the first 5 rows, then keep the second 5 rows. 
If you have two queries that returns the same row fields, you can merge their outputs together with the UNION operator. The following tutorial exercise shows you how to use the UNION operator: 
SELECT * FROM fyi_links WHERE tag = 'DBA'
GO
id   url                notes  counts  created     tag
102  dba.fyicenter.com  NULL   972     2007-05-19  DBA
104  www.mysql.com             390     2006-01-01  DBA
105  www.oracle.com            960     2005-01-01  DBA

SELECT * FROM fyi_links WHERE tag = 'DEV'
GO
id   url                notes  counts  created     tag
101  dev.fyicenter.com  NULL   120     2006-04-30  DEV
106  www.php.net               439     2004-01-01  DEV

SELECT * FROM fyi_links WHERE tag = 'DBA'
UNION
SELECT * FROM fyi_links WHERE tag = 'DEV'
GO
id   url                notes  counts  created     tag
102  dba.fyicenter.com  NULL   972     2007-05-19  DBA
104  www.mysql.com             390     2006-01-01  DBA
105  www.oracle.com            960     2005-01-01  DBA
101  dev.fyicenter.com  NULL   120     2006-04-30  DEV
106  www.php.net               439     2004-01-01  DEV
If you need to sort the output from two queries grouped together with a UNION operator, you need to apply the ORDER BY clause at the group level, not at the subquery level. 
Note that SQL Server and MySQL react differently to the ORDER BY clause used in a subquery of a UNION operator: 
SQL Server will give error if ORDER BY is used inside a subquery of a UNION operator. 
MySQL will ignore the ORDER BY clause inside a subquery of a UNION operator. 
The following tutorial exercise shows you how to use ORDER BY clause with UNION operator: 
(SELECT * FROM fyi_links WHERE tag = 'DBA'
   ORDER BY created)
UNION
(SELECT * FROM fyi_links WHERE tag = 'DEV'
   ORDER BY created)
GO
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ORDER'.

(SELECT * FROM fyi_links WHERE tag = 'DBA')
UNION
(SELECT * FROM fyi_links WHERE tag = 'DEV')
ORDER BY created
GO
id   url                notes  counts  created     tag
106  www.php.net               439     2004-01-01  DEV
105  www.oracle.com            960     2005-01-01  DBA
104  www.mysql.com             390     2006-01-01  DBA
101  dev.fyicenter.com  NULL   120     2006-04-30  DEV
102  dba.fyicenter.com  NULL   972     2007-05-19  DBA
Note that the ORDER BY works nicely. Rows returned from both subqueries are sorted together. 


 discuss this topic to forum
[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]

SQL Server FAQs - Understanding SELECT Statements with Joins and Subqueries

relation tutorial

Category

New

Hot