Posted by Erland Sommarskog on 08/18/07 21:14

Yas (yasar1@gmail.com) writes:
> Hi, is there a way I can check if a table is empty and contains no
> rows?
>
> I have a Table1 which being dynamic can sometimes end up with now
> columns what so ever. I'm using Table1 in one of my views along with 2
> other tables and I would like put a condition at the...something like
> AND Table1 IS NOTEMPTY
>

AND EXISTS (SELECT * FROM Table1)

The EXISTS / NOT EXISTS are part of the essential SQL anyone who is
working with SQL should learn. The above example is simple, but say
you want all customers who have placed an order / who have never placed
an order:

SELECT C.CustomerID, C.CompanyName
FROM Customers C
WHERE /* NOT */ EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)

You may be used to using IN / NOT IN for this, but (NOT) IN is limited,
as you get problem if you have a multi-column key. Plus that NOT IN
can trap you if NULL values are involved.

The query above runs in the Northwind database, if you want to try it.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]