Using an inner select construction for a select value

Question

I would like to hear if anyone can tell me a simple syntax that accomplishes the same as the following (with the same flexibility):

SELECT C.CompanyName,
(SELECT Count(*) FROM Employees WHERE CompanyId = C.Id) as EmployeeCount
FROM Company C

Now, what's important is that the inner SELECT giving the EmployeeCount is:

An independent SELECT statement
- This means that it should work with any existing SELECT, even if it already contains joins etc.
Can use values from the parent SELECT

I know that this scenario can be easily accomplished in other ways, but the above is a simplified example to explain the challenge. My real scenario is a complex SELECT statement where I do not want to complicate it by adding more joins. Performance is no issue.

Thanks guys - I was thinking there was some obvious syntax that I didn't know about, that would be perfect for this scenario - I guess good ole' joins is the answer ... — Niels Brinch, Dec 11 '11 at 17:30

aF. · Accepted Answer · 2011-12-11T18:30:14.557

3

Using INNER JOIN:

SELECT C.CompanyName, Count(E.*) as EmployeeCount
FROM Company C
INNER JOIN Employees E on E.CompanyId = C.Id

Using NESTED JOIN:

SELECT C.CompanyName, Count(E.1) as EmployeeCount
FROM Company C, Employess E
WHERE E.CompanyId = C.Id

If you want to use the same syntax, at least put this:

SELECT C.CompanyName,
(SELECT Count(1) FROM Employees WHERE CompanyId = C.Id) as EmployeeCount
FROM Company C

If you need all the data to be shown, even the ones the companies without any Employees, you can use a LEFT OUTER JOIN:

SELECT C.CompanyName, Count(E.*) as EmployeeCount
FROM Company C
LEFT OUTER JOIN Employees E on E.CompanyId = C.Id

edited Dec 11 '11 at 18:30

answered Dec 11 '11 at 17:01

aF.

64,980
43
135
198

Nice overview of good alternatives - however, they are not completely independent with those joins, it seems - I mean, depending on the existing SELECT, I could ruin it with this, couldn't I? Basically, I want to add a field to the SELECT without risking to introduce an error in the SELECT – Niels Brinch Dec 11 '11 at 17:13
Try to use the `left outer join` approach. Add it in the end :) or if you don't have performance issues, you can use your approach ^^ – aF. Dec 11 '11 at 17:20
Thanks. Is there no cases where adding an extra `left outer join` can interfere with the existing SELECT? – Niels Brinch Dec 11 '11 at 17:23
Use GROUP BY C.CompanyName if you want only one line per company. Otherwise you get on line per employee. Use LEFT OUTER JOIN if you have companies with no employees. Otherwise they will not appear in the result set. – Olivier Jacot-Descombes Dec 11 '11 at 17:25
@NielsBrinch the left outer join selects the data from the table if exist a connection between them and selects NULL otherwise :) – aF. Dec 11 '11 at 17:31
2

`select Count(1) from t` will **not** be faster than `select count(*) from t`. That's nonsense. – Dec 11 '11 at 17:41
No difference between count(1) and count(*). http://stackoverflow.com/questions/1221559/count-vs-count1 – Mikael Eriksson Dec 11 '11 at 17:48

score 1 · Answer 2 · answered Dec 11 '11 at 17:01

1

Try using a derived table, which statifies both your conditions.

An independent SELECT statement.
a. Using a Derived Table allows you to keep your independent Select Statement
Can use values from the parent SELECT.

a. As an Inner join you can still use values from the parent select.

SELECT 
  C.CompanyName, 
  EC.EmployeeCount
FROM Company C 
INNER JOIN (SELECT 
              Count(*) AS EmployeeCount 
            FROM Employees ) EC 
  ON WHERE EC.CompanyId = C.Id

answered Dec 11 '11 at 17:01

John Hartsock

85,422
23
131
146

I know that I haven't really been to clear in my question. Sorry. By independent, I mean that it should not be possible for it to interfere with anything else going on in the select, besides returning an extra value in the SELECT. – Niels Brinch Dec 11 '11 at 17:15

score 1 · Answer 3 · answered Dec 11 '11 at 17:32

If your inner select is complicated, then why not make a view of it:

CREATE VIEW EmpSelect AS
    SELECT CompanyId, whatever FROM Employees;

Then

SELECT
    C.CompanyName, Count(*) AS EmpCount
FROM
    Company C
    LEFT JOIN EmpSelect E
        ON C.Id = E.CompanyId
GROUP BY
    C.CompanyName;

Using an inner select construction for a select value

3 Answers3