SQL - How to optimize the query

Question

I have wrote the following query which extracts all users which are in child departments of current user's department. Current user cames from client app, but I Decalred it here in SQL for tests reason.

DECLARE @UserID INT = 72

SELECT * 
FROM users
WHERE Department_Id IN ( 
                        SELECT DISTINCT Id    /*, idp*/
                        FROM departments
                        WHERE idp IN (
                                        SELECT Department_Id 
                                        FROM users 
                                        WHERE Id = @UserID
                                        )
                        )

OR Department_Id IN (
                        SELECT DISTINCT idp
                        FROM departments
                        WHERE idp IN (
                                        SELECT Department_Id 
                                        FROM users 
                                        WHERE Id = @UserID
                                        )
                        )

I wanted to select the Id and the idp from departments to have a short query, but when i use this way it returns me an SQL error :

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

This is because my list should contain only one column, but not 2.

Please advice me any way to reduce this query, especially the second part (after OR) which is a copy-paste of first (before OR)

Thank you.

Hiya, you can have multiple distinct in same statement see here - http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns-postgresql - hope this helps, cheers — Tats_innit, Mar 12 '12 at 12:31
So this is not about optimizing query performance, but about "optimizing" the query **syntax**, right? — Branko Dimitrijevic, Mar 12 '12 at 12:36

score 3 · Answer 1 · answered Mar 12 '12 at 12:30

3

Try using EXISTS like this

SELECT * 
FROM   users u
WHERE  EXISTS(  SELECT *
                FROM   departments
                WHERE  idp IN (SELECT Department_Id FROM users WHERE Id = @UserID)
                       AND (id = u.Department_Id
                           OR idp = u.Department_Id)    )

answered Mar 12 '12 at 12:30

wqw

11,771
1
33
41

To reduce the number of lines of code further you could replace `AND (id = u.Department_Id OR idp = u.Department_Id)` in the exists subquery with `u.Department_Id IN (ID, IDP)`. – GarethD Mar 12 '12 at 12:55

score 2 · Accepted Answer · answered Mar 12 '12 at 13:00

A few thoughts...

Nested IN subqueries are unlikely to be friendly.
You don't need DISTINCT when using IN

I'd use GROUP BY to deal with the 1:many relationships, but as your answer is using an alternative structure, I'll try to keep close to what you have...

DECLARE @UserID INT = 72

SELECT
  *
FROM
  users AS associates
WHERE
  EXISTS (
    SELECT 
      *
    FROM
      users
    INNER JOIN      
      departments
        ON departments.idp = users.Department_Id
    WHERE
      users.id = @user_id
      AND (   departments.id  = associates.department_id
           OR departments.idp = associates.department_id)
  )

If you did use the GROUP BY approach, you avoid sub-queries and correlated-sub-queries all together...

DECLARE @UserID INT = 72

SELECT
  associates.id
FROM
  users
INNER JOIN      
  departments
    ON departments.idp = users.Department_Id
INNER JOIN
  users AS associates
    ON associates.department_id = departments.id
    OR associates.department_id = departments.idp
WHERE
  users.id = @user_id
GROUP BY
  associates.id

If there any other fields in associates that you need, just add them to the SELECT and the GROUP BY.

Kaf · Answer 3 · 2012-03-12T12:47:12.200

1

SELECT * 
FROM users
WHERE Department_Id IN ( 
                    SELECT myId FROM 
                     ( SELECT Id AS myId
                       FROM departments
                       UNION ALL
                       SELECT idp AS myId
                       FROM departments
                     ) A 
                    WHERE A.myId IN (
                                    SELECT Department_Id 
                                    FROM users 
                                    WHERE Id = @UserID
                                    )
                    )

edited Mar 12 '12 at 12:47

answered Mar 12 '12 at 12:34

Kaf

33,101
7
58
78

**@Indikaf**, this query returns only one item which is the parent. I found that the answer offered by **wqw** is a good answer – mihai Mar 12 '12 at 12:51

SQL - How to optimize the query

3 Answers3