Skip to content

Frequently Asked Questions - SQL Server SELECT Statement

What's the equivalent of Oracle's DUAL table in SQL Server?

There is no equivalent system table in SQL Server for Oracle's DUAL table. To select constants or literals in Oracle, the DUAL table is used as follows:

SELECT SYSDATE AS [CurrentDateTime] FROM DUAL

In SQL Server, the equivalent of this statement is as follows:

SELECT GETDATE() AS [CurrentDateTime]

Basically to select constants or literals in SQL Server, just use the SELECT statement without the FROM clause.

How can I return records from one table if it exists on another table?

There are three ways of returning the records from a table that exist in another table based on a key or ID, namely by using the IN, EXISTS and INNER JOIN.

Using the Northwind database, to determine the Customers with Orders, here's how it is done using the IN method:

SELECT * FROM [dbo].[Customers]
WHERE [CustomerID] IN (SELECT [CustomerID] FROM [dbo].[Orders])

Here's how the query will look like using the EXISTS method:

SELECT * FROM [dbo].[Customers] C
WHERE EXISTS (SELECT 'X' FROM [dbo].[Orders] O
              WHERE C.[CustomerID] = O.[CustomerID])

Lastly, here's how the query will look like using the INNER JOIN method: 

SELECT DISTINCT C.* 
FROM [dbo].[Customers] C INNER JOIN [dbo].[Orders] O
  ON C.[CustomerID] = O.[CustomerID]

The DISTINCT clause in the INNER JOIN method is necessary especially since a customer can have multiple orders in the [dbo].[Orders] table. Without the DISTINCT clause, it will return multiple records for the same customer.

All these three methods will return 89 records. Of the three methods, the slowest method in terms of performance is the IN method. Between the EXISTS and the INNER JOIN methods, the EXISTS method has a slight advantage over the INNER JOIN basically because of the DISTINCT clause which slows down its performance.

How can I return records from one table that does not exist in another table?

There are three ways of returning the records from a table that does not exist in another table based on a key or ID, namely by using the NOT IN, NOT EXISTS and LEFT OUTER JOIN.

Using the Northwind database, to determine the Customers without any Orders, here's how it is done using the NOT IN method:

SELECT *
FROM [dbo].[Customers]
WHERE [CustomerID] NOT IN (SELECT [CustomerID] FROM [dbo].[Orders])

Here's how the query will look like using the NOT EXISTS method:

SELECT *
FROM [dbo].[Customers] C
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Orders] O
                  WHERE C.[CustomerID] = O.[CustomerID])

Lastly, here's how the query will look like using the LEFT OUTER JOIN method:

SELECT * 
FROM [dbo].[Customers] C LEFT OUTER JOIN [dbo].[Orders] O
  ON C.[CustomerID] = O.[CustomerID]
WHERE O.[CustomerID] IS NULL

Of the three methods, the method that gives the least performance is the NOT IN method while the method that gives the best performance is the LEFT OUTER JOIN method. For small tables, the difference in performance among the three methods is negligible but for tables with a lot of records the difference in performance is noticeable.

If the tables being compared have an index on the column that is being joined (in this case the [CustomerID] of both [dbo].[Customers] and [dbo].[Orders] tables), then the LEFT OUTER JOIN is a better choice than the NOT EXISTS. On the other hand, if only the second table has an index on the column that is being checked for existence (in this case the [dbo].[Orders] table), then the NOT EXISTS may provide a better performance.

How can I identify duplicate records in a table?

To identify duplicate records in a table, you will use the GROUP BY and HAVING clause of the SELECT statement. Let's say you have a table of Accounts and you want to determine the duplicate records based on the first name, last name and date of birth. Your SQL statement will look as follows:

SELECT [FirstName], [LastName], [BirthDate]
FROM [dbo].[Accounts]
GROUP BY [FirstName], [LastName], [BirthDate]
HAVING COUNT(*) > 1

This query will give you the duplicate records based on the FirstName, LastName and BirthDate. If you want to know how many times those records are duplicated in the table, you can simply include the COUNT(*) in the output:

SELECT [FirstName], [LastName], [BirthDate], COUNT(*) AS [DuplicateCount]
FROM [dbo].[Accounts]
GROUP BY [FirstName], [LastName], [BirthDate]
HAVING COUNT(*) > 1

If you want the result sorted by the most duplicated combination of the FirstName, LastName and BirthDate, you can include the ORDER BY clause as follows:

SELECT [FirstName], [LastName], [BirthDate], COUNT(*) AS [DuplicateCount]
FROM [dbo].[Accounts]
GROUP BY [FirstName], [LastName], [BirthDate]
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

How can I split a full name into a first name and last name?

There are different ways of splitting a full name into the corresponding first name and last name. The following link discusses three methods of performing such task, using the SUBSTRING string function, the LEFT/RIGHT string function and the PARSENAME function:

Tips and Tricks - Split Name

How can I select only a certain number of records from a table?

There are two ways of returning only a certain number of records from a table. The first method is with the use of the TOP clause of the SELECT statement:

SELECT TOP 10 * FROM [dbo].[Customers]

The second method is with the use of the SET ROWCOUNT to set the number of records to return:

SET ROWCOUNT 10

SELECT * FROM [dbo].[Customers]

SET ROWCOUNT 0

It is important that after setting the ROWCOUNT that you set it back to 0 so that any statements executed after this statement will return all rows. It should be noted that the SET ROWCOUNT affects all statements, including SELECT statements as part of a sub-query.

One advantage of the SET ROWCOUNT over the TOP clause is that you can specify a variable instead of just a literal. So if you want the number of records to be returned by your SELECT statement to be different every time you execute it based on a variable, you have to use the SET ROWCOUNT because the TOP clause can only accept a literal value.

DECLARE @RowCount INT
SET @RowCount = 20
SET ROWCOUNT @RowCount
SELECT * FROM [dbo].[Customers]
SET ROWCOUNT 0

If you pass a variable in the TOP clause, as follows:

DECLARE @RowCount INT
SET @RowCount = 20
SELECT TOP @RowCount * FROM [dbo].[Customers]

You will get the following error message:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '@RowCount'.

If you want to generate random records from the table, the following link discusses how to do this:

Tips and Tricks - Generate Random Records

How can I select all transactions for the day?

To return all transactions for the day, you need to get just the date part of the current system date, GETDATE(). There are a few ways of getting just the date part of any date and this is discussed in the following link:

User-Defined Functions - Get Date Only

To get all orders for today, your query will look like the following:

SELECT * FROM [dbo].[Orders]
WHERE [OrderDate] >= DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

The DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE())) returns just the date part of the current system date removing the time part and making it 00:00:00.

Using the user-defined function discussed in the link mentioned above, the query to get all orders for today will be as follows:

SELECT * FROM [dbo].[Orders]
WHERE [OrderDate] >= [dbo].[ufn_GetDateOnly](GETDATE())

How can I sort the result of my select statement based on 2 columns?

To sort the output of a SELECT statement based on 2 or more columns, you simply include all columns you want sorted in the ORDER BY clause of the SELECT statement separating each column by a comma. Using the Northwind database, to retrieve all rows in the [dbo].[Orders] table sorted by the CustomerID and OrderDate, your SELECT statement will look like the following:

SELECT * FROM [dbo].[Orders]
ORDER BY [CustomerID], [OrderDate]

If you want the OrderDate sorted in descending order, simply include DESC after the column, as follows:

SELECT * FROM [dbo].[Orders]
ORDER BY [CustomerID], [OrderDate] DESC

If the sort order is not specified, the result is sorted in ASCENDING order.

How can I count the number of rows in a table?

To get the number of rows in a table, you will use the COUNT aggregate function.

SELECT COUNT(*) AS [TransactionCount] FROM [dbo].[Transactions]

The COUNT aggregate function returns the number of items in a group. COUNT(*) specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain NULL values.

How can I avoid the error "Division by zero encountered" and just return 0 if the denominator is 0?

There are two ways to avoid the "Division by zero encountered" error in your SELECT statement and this is by using the CASE statement and the NULLIF/ISNULL functions. Using the CASE statement, your query will look like the following:

SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]

Using the NULLIF and ISNULL functions, your query will look like the following:

SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]

What this does is change the denominator into NULL if it is zero. Then in the division, any number divided by NULL results into NULL. So if the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, the ISNULL function is used.