MS SQL Server

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 ggl_rates table.

SELECT id, url, tag, YEAR(created) As year

FROM ggl_links WHERE id IN (SELECT id FROM ggl_rates)

GO

id url tag Year

101 www.rendc.org main 2006

102 www.rendc.org/html HTMLA 2007

103 www.rendc.org/sql SQL 2007

SELECT id, url, tag, YEAR(created) As year

FROM ggl_links

WHERE id IN (101, 102, 103, 204, 205, 206, 207)

GO

id url tag Year

101 www.rendc.org main 2006

102 www.rendc.org/html HTMLA 2007

103 www.rendc.org/sql SQL 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 ggl_links table that there are rows existing in the ggl_rates table with the same id.

SELECT id, url, tag, YEAR(created) As year

FROM ggl_links WHERE EXISTS (

SELECT * FROM ggl_rates

WHERE ggl_rates.id = ggl_links.id)

GO

id url tag Year

101 www.rendc.org main 2006

102 www.rendc.org/html DBA 2007

103 www.rendc.org/sql SQL 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 ggl_links l LEFT OUTER JOIN ggl_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 ggl_links l LEFT OUTER JOIN ggl_rates r

ON l.id = r.id) s WHERE s.url LIKE '%er%'

GO

101 www.rendc.org The best

102 www.rendc.org/html Well done

103 www.rendc.org/xml Thumbs up

107 www.rendc.org/sql 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 ggl_links GROUP BY tag,

YEAR(created)

GO

Category Year Counts

HTML 2003 1

XML 2004 1

CSS 2005 1

SQL 2006 1

SEO 2006 1

JavaScript 2007 1

JOBS 2007 1

SELECT COUNT(*) FROM (

SELECT tag AS Category, YEAR(created) AS Year,

COUNT(*) AS Counts FROM ggl_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 ggl_links:

SELECT TOP 5 id, url, counts, tag FROM ggl_links

ORDER BY counts DESC

GO

id url counts tag

102 www.rendc.org 972 DBA

105 www.google.com 960 DBA

107 www.yahoo.com 828 SQA

103 www.mysql.com 728 SQA

106 www.php.net 439 DEV

SELECT TOP 3 id, url, counts, tag FROM ggl_links

ORDER BY counts DESC

GO

id url counts tag

102 www.rendc.org 972 DBA

105 www.google.com 960 DBA

107 www.yahoo.com 828 SQA

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 ggl_links WHERE tag = 'DBA'

GO

id url notes counts created tag

102 rendc.org 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 ggl_links WHERE tag = 'DEV'

GO

id url notes counts created tag

101 rendc.org/html NULL 120 2006-04-30 DEV

106 www.php.net 439 2004-01-01 DEV

SELECT * FROM ggl_links WHERE tag = 'DBA'

UNION

SELECT * FROM ggl_links WHERE tag = 'DEV'

GO

id url notes counts created tag

102 rendc.org 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 rendc.org/html NULL 120 2006-04-30 DEV

106 www.php.net 439 2004-01-01 DEV

If you want to continue with other tutorial exercises in this FAQ collection, you need to add more data to the testing table. Follow the script below to add a new column and more rows:

ALTER TABLE ggl_links ADD tag VARCHAR(8)

GO

UPDATE ggl_links SET tag = 'DEV' WHERE id = 101

GO

UPDATE ggl_links SET tag = 'DBA' WHERE id = 102

GO

UPDATE ggl_links SET tag = 'SQA' WHERE id = 103

GO

INSERT INTO ggl_links VALUES (104,

'www.mysql.com', '', '0', '2006-01-01', 'DBA')

GO

INSERT INTO ggl_links VALUES (105,

'www.oracle.com', '', '0', '2005-01-01', 'DBA')

GO

INSERT INTO ggl_links VALUES (106,

'www.php.net', '', '0', '2004-01-01', 'DEV')

GO

INSERT INTO ggl_links VALUES (107,

'www.winrunner.com', '', '0', '2003-01-01', 'SQA')

GO

UPDATE ggl_links

SET counts = CAST(LOG(id)*1000000 AS INT) % 1000

GO

If you don't want select all rows from a table, you can specify a WHERE clause to tell the query to return only the rows that meets the condition defined in the WHERE clause. The WHERE clause condition is a normal Boolean expression. If any data from the table needs to be used in the Boolean expression, column names should be used to represent the table data.

The first select statement below only returns rows that have url names containing the letter "a". The second select statement returns no rows, because the WHERE clause results FALSE for all rows in the table.

SELECT * FROM ggl_links WHERE url LIKE '%s%'

GO

id url notes counts created

102 rendc.org/sql NULL 0 2007-05-19

103 rendc.org/xslt NULL NULL 2007-05-19

SELECT * FROM ggl_links WHERE id < 100

GO

0 row

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 ggl_links WHERE tag = 'DBA'

ORDER BY created)

UNION

(SELECT * FROM ggl_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 ggl_links WHERE tag = 'DBA')

UNION

(SELECT * FROM ggl_links WHERE tag = 'DEV')

ORDER BY created

GO

The SELECT statement is also called the query statement. It is the most frequently used SQL statement in any database application. SELECT statements allows you to retrieve data from one or more tables or views, with different selection criteria, grouping criteria and sorting orders.

A SELECTE statement has the following basic syntax:

SELECT select_list

FROM table_source

WHERE search_condition

GROUP BY group_by_expression

HAVING search_condition

ORDER BY order_by_expression

Here is an example of a SELECT statement with all clauses mentioned above:

SELECT SalesOrderID, SUM(LineTotal) AS TotalPrice

FROM SalesLT.SalesOrderDetail

WHERE ModifiedDate > '2004-05-01'

GROUP BY SalesOrderID

HAVING COUNT(*) > 30

ORDER BY TotalPrice DESC

If you want the returning rows to be sorted, you can specify a sorting expression in the ORDER BY clause. The simplest sort expression is column name who's values will be sorted by. The following select statement returns rows sorted by the values in the "counts" column:

SELECT * FROM ggl_links ORDER BY counts

GO

id url notes counts created tag

101 rendc.org NULL 120 2006-04-30 DEV

104 www.mysql.com 390 2006-01-01 DBA

106 www.php.net 439 2004-01-01 DEV

103 rendc.org/html NULL 728 2007-05-19 SQA

107 www.winrunner.com 828 2003-01-01 SQA

105 www.oracle.com 960 2005-01-01 DBA

102 rendc.org/sql NULL 972 2007-05-19 DBA

The output is sorted by the values of the "counts" column.

If there are duplications in the returning rows, and you want to remove the duplications, you can use the keyword DISTINCT in the SELECT clause. The DISTINCT applies to the combination of all data fields specified in the SELECT clause. The tutorial exercise below shows you how DISTINCT works:

CREATE TABLE ggl_team (first_name VARCHAR(8),

last_name VARCHAR(8))

GO

INSERT INTO ggl_team VALUES ('John', 'Gate')

GO

INSERT INTO ggl_team VALUES ('John', 'Russell')

GO

INSERT INTO ggl_team VALUES ('John', 'Seo')

GO

INSERT INTO ggl_team VALUES ('John', 'Gate')

GO

INSERT INTO ggl_team VALUES ('James', 'Gate')

GO

INSERT INTO ggl_team VALUES ('Peter', 'Gate')

GO

INSERT INTO ggl_team VALUES ('John', 'Gate')

GO

If a group function is used in the SELECT clause, all other selection fields must be group level fields. Non-group fields can not be mixed with group fields in the SELECT clause. The script below gives you an example of invalid SELECT statements with group and non-group selection fields mixed in a SELECT statement:

SELECT COUNT(*), url FROM ggl_links

GO

Msg 8120, Level 16, State 1, Line 1

Column 'ggl_links.url' is invalid in the select list because

it is not contained in either an aggregate function or the

GROUP BY clause.

SELECT 2*COUNT(*), 2*counts FROM ggl_links

GO

Msg 8120, Level 16, State 1, Line 1

Column 'ggl_links.counts' is invalid in the select list

because it is not contained in either an aggregate function

or the GROUP BY clause.

In these examples, COUNT(*) is a group field and "url"/"2*counts" is a non-group field. The error message also tells that "url"/"counts" is not an aggregate function (group function).