CODEWITHSUNDEEP
sqlsql-server
Growstrong
Growstrong

Reputation: 37

SQl ANY or ALL Clause

I have table which I query to get few columns to load data to application.

Suppose I have following columns from a select query:

Account| Employee | Amount | Position
123    | EMP123   | 1000   |
143    | EMP123   | 1000   |
153    | EMP123   | 1000   |
163    | EMP123   | 1000   |
100    | EMP456   | 1000   |
143    | EMP456   | 1000   |
153    | EMP456   | 1000   |
163    | EMP456   | 1000   |

I want to select position of employee based on account 123.

So for all those employees which have account 123 in records return their position as for example Temp else Perm.

So expected output based on example above;

Account| Employee | Amount | Position
123    | EMP123   | 1000   | Temp
143    | EMP123   | 1000   | Temp
153    | EMP123   | 1000   | Temp
163    | EMP123   | 1000   | Temp
100    | EMP456   | 1000   | Perm
143    | EMP456   | 1000   | Perm
153    | EMP456   | 1000   | Perm
163    | EMP456   | 1000   | Perm

I have got the result using ANY clause but that is very very very slow and i have more than 100000 records :|

Query i use;

Select ACCOUNT,amount,EMPLOYEE,
  CASE 
  WHEN EMPLOYEE = ANY (SELECT EMPLOYEE FROM Table1 WHERE ACCOUNT = 123) 
  THEN 'Temp'
  ELSE 'Perm'
  END AS 'Position'

Any tip would be greatly appreciated!!

Upvotes: 0

Views: 416

Answers (5)

Dumi
Dumi

Reputation: 1434

Try this query. 

SELECT ACCOUNT,amount,EMPLOYEE
, CASE WHEN ISNULL(T2.HasAnyPosition, 0) > 0 THEN 'Perm' 
ELSE 'Temp' END AS Position
FROM EmployeeTable T1
OUTER APPLY
(
 SELECT COUNT(0) HasAnyPosition
 From PositionTable T2
 WHERE T2.Account = T1.Account
) T2

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

You could try using exists:

select Account,
       Employee,
       Amount,
       case when exists(select 1 from my_table
                        where Employee = t.Employee
                          and Account = 123)
            then 'Temp' else 'Perm' end Position
from my_table t

Moreover, you could create indexes on Account and Employee columns.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

Here's an example using a windowed aggregate:

declare @t table(Account int, Employee char(6), Amount int)
insert into @t(Account, Employee, Amount) values
(123,'EMP123',1000),
(143,'EMP123',1000),
(153,'EMP123',1000),
(163,'EMP123',1000),
(100,'EMP456',1000),
(143,'EMP456',1000),
(153,'EMP456',1000),
(163,'EMP456',1000)

select
    *,
    MAX(CASE WHEN Account='123' THEN 'Temp' ELSE 'Perm' END) OVER (PARTITION BY Employee)
from @t

As I said in the comments though, it's doubtful that any re-write will offer a massive performance boost and it's more likely that you're missing appropriate indexes. If it's egregious enough, when you generate an execution plan for either query (the one from your question or the one here), the system should highlight a missing index1.

Result:

Account     Employee Amount      
----------- -------- ----------- ----
123         EMP123   1000        Temp
143         EMP123   1000        Temp
153         EMP123   1000        Temp
163         EMP123   1000        Temp
100         EMP456   1000        Perm
143         EMP456   1000        Perm
153         EMP456   1000        Perm
163         EMP456   1000        Perm

1Note that the suggestions aren't always, how shall we say, stellar. But if it's saying there's a missing index, it's usually right that some additional index(es) will improve your query times. But don't blindly apply all such suggestions.

Upvotes: 1

Nikhil
Nikhil

Reputation: 3950

  Select ACCOUNT,amount,EMPLOYEE,
  CASE 
  WHEN EMPLOYEE like '%123%' then 'temp' else 'perm' end as position
  from t

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can use in

Select ACCOUNT,amount,EMPLOYEE,
  CASE 
  WHEN EMPLOYEE in (SELECT EMPLOYEE FROM Table1 WHERE ACCOUNT = 123) 
  THEN 'Temp'
  ELSE 'Perm'
  END AS 'Position'

Upvotes: 0

Related Questions