How do I use T-SQL's Exists keyword?

Question

I have a query I want to run as a subquery that will return a set of FK's. With them I want to return only rows that has a matching key.

Subquery:

SELECT ID  FROM tblTenantTransCode  WHERE     tblTenantTransCode.CheckbookCode =        (SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income') 

That will return all the transaction codes that have a checkbook code that matches Rent Income

Now I want to select All Transactions where their transactioncode matches an ID returned in the subquery. I've gotten this far, but SQL Server complains of a syntax error. How can I do this?

Full Query:

SELECT *  FROM tblTransaction WHERE     tblTransaction.TransactionCode IN        (SELECT ID FROM tblTenantTransCode         WHERE tblTenantTransCode.CheckbookCode =             (SELECT ID FROM tblCheckbookCode WHERE Description = 'Rent Income')) 

Tables:

 tblCheckbookCode      ID      Description      Other Info    tblTenantTransCode      ID      CheckbookCode <-- fk we're looking for                         in the tblCheckbookCode.                         We're selecting only checkbook codes                         that have the Description 'Rent Income'      Other Info    tblTransactions      ID      TransactionCode <-- fk to tenant transaction code.                           We're looking for an ID that is returned                           in the above query/join   

Answer 1

To answer your question about using the EXISTS keyword, here is an example query that uses an EXISTS predicate, based on the query as currently given in your question.

     SELECT t.*       FROM tblTransaction t      WHERE EXISTS            (               SELECT 1                FROM tblTenantTransCode ttc                JOIN tblCheckbookCode cc                  ON (cc.ID = ttc.CheckbookCode AND cc.Description='Rent Income')               WHERE ttc.ID = t.TransactionCode            ) 

---

Additional Details:

We all recognize that there are a variety of SQL statements that will return the result set that meets the specified requirements. And there are likely going to be differences in the observed performance of those queries. Performance is particularly dependent on the DBMS, the optimizer mode, the query plan, and the statistics (number of rows and data value distribution).

One advantage of the EXISTS is that it makes clear that we aren't interested returning any expressions from tables in the subquery. It serves to logically separate the subquery from the outer query, in a way that a JOIN does not.

Another advantage of using EXISTS is that avoids returning duplicate rows that would be (might be) returned if we were to instead use a JOIN.

An EXISTS predicate can be used to test for the existence of any related row in a child table, without requiring a join. As an example, the following query returns a set of all orders that have at least one associated line_item:

     SELECT o.*       FROM order o      WHERE EXISTS            ( SELECT 1                FROM line_item li               WHERE li.order_id = o.id            ) 

Note that the subquery doesn't need to find ALL matching line items, it only needs to find one row in order to satisfy the condition. (If we were to write this query as a JOIN, then we would return duplicate rows whenever an order had more than one line item.)

A NOT EXISTS predicate is also useful, for example, to return a set of orders that do not have any associated line_items.

     SELECT o.*       FROM order o      WHERE NOT EXISTS            ( SELECT 1                FROM line_item li               WHERE li.order_id = o.id            ) 

Of course, NOT EXISTS is just one alternative. An equivalent result set could be obtained using an OUTER join and an IS NULL test (assuming we have at least one expression available from the line_item table that is NOT NULL)

     SELECT o.*       FROM order o       LEFT       JOIN line_item li ON (li.order_id = o.id)      WHERE li.id IS NULL 

There seems to be a lot of discussion (relating to answers to the original question) about needing to use an IN predicate, or needing to use a JOIN.

Those constructs are alternatives, but aren't necessary. The required result set can be returned by a query without using an IN and without using a JOIN. The result set can be returned with a query that uses an EXISTS predicate. (Note that the title of the OP question did ask about how to use the EXISTS keyword.)

Here is another alternative query (this is not my first choice), but the result set returned does satisfy the specified requirements:

      SELECT t.*       FROM tblTransaction t      WHERE EXISTS            (               SELECT 1                FROM tblTenantTransCode ttc               WHERE ttc.ID = t.TransactionCode                 AND EXISTS                      (                       SELECT 1                          FROM tblCheckbookCode cc                        WHERE cc.ID = ttc.CheckbookCode                          AND cc.Description = 'Rent Income'                     )            )  

Of primary importance, the query should return a correct result set, one that satisfies the specified requirements, given all possible sets of conditions.

Some of the queries presented as answers here do NOT return the requested result set, or if they do, they happen to do so by accident. Some of the queries will work if we pre-assume something about the data, such that some columns are UNIQUE and NOT NULL.

Performance differences

Sometimes a query with an EXISTS predicate will not perform as well as a query with a JOIN or an IN predicate. In some cases, it may perform better. (With the EXISTS predicate, the subquery only has to find one row that satisfies the condition, rather than finding ALL matching rows, as would be required by a JOIN.)

Performance of various query options is best gauged by observation.