Is this the best I can do with my SQL Server query that pulls data from multiple table?

Question

I would like to think of myself as a decent SQL Server programmer but this query always stumps me. I'm sure there is a much better way to query this.

SELECT DISTINCT
    database_name,
    CASE 
        WHEN ((SELECT MAX(1) FROM dbo.column_list WHERE column_list IS NOT NULL and database_id = d.database_id) = 1 
                OR (SELECT MAX(1) FROM dbo.table_list WHERE table_list IS NOT NULL and database_id = d.database_id) = 1) 
            THEN 1 
            ELSE 0 
    END AS 'has_definition'
FROM dbo.database_list d;

I have 3 tables database_list, table_list and a column_list. I'm trying to query for a given database in the database list to see if any of the underlying table or column has definitions in them. This query works now and returns a unique list of databases and a has_definition but this seems wrong to me.

Any thoughts would be helpful.

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

I'd change it to use EXISTS(SELECT * ....) OR EXISTS(SELECT * ....) rather than the SELECT(MAX) business but apart from that looks OK to me.

SQL Server will probably show a plan with a concatenation operator that short circuits the OR as per my tests here. I also found that it would test the cheaper one to evaluate first in this case irrespective of order of the clauses.

edited May 23 '17 at 11:56

Community

1
1

answered Mar 21 '12 at 15:24

Martin Smith

438,706
87
741
845

score 1 · Accepted Answer · answered Mar 21 '12 at 15:23

You might try this version instead:

SELECT database_name,
    CASE WHEN EXISTS 
    (
      SELECT 1 FROM dbo.column_list 
         WHERE column_list IS NOT NULL and database_id = d.database_id
      UNION ALL
      SELECT 1 FROM dbo.table_list 
         WHERE table_list IS NOT NULL and database_id = d.database_id
    ) 
            THEN 1 
            ELSE 0 
    END AS [has_definition]
FROM dbo.database_list AS d;

And maybe compare it to this:

SELECT database_name,
    CASE WHEN EXISTS 
    (
      SELECT 1 FROM dbo.column_list 
         WHERE column_list IS NOT NULL and database_id = d.database_id
    ) 
    OR EXISTS
    (
      SELECT 1 FROM dbo.table_list 
         WHERE table_list IS NOT NULL and database_id = d.database_id
    ) 
            THEN 1 
            ELSE 0 
    END AS [has_definition]
FROM dbo.database_list AS d;

I don't think you'll need the distinct unless you really have duplicates in dbo.database_list.

Also please don't use 'single quotes' for aliases - this syntax is deprecated, you should use [square brackets] instead.

Is there a way to doing this by joining 3 tables? I thought we could but couldnt wrap my head around the logic — darwindeeds, Mar 21 '12 at 16:00
Not sure I understand. Can't you add more `EXISTS` clauses? You'll need to add information to the question about these other tables and how they're related in order to get any useful input. — Aaron Bertrand, Mar 21 '12 at 16:37

Is this the best I can do with my SQL Server query that pulls data from multiple table?

2 Answers2