SQL performance of a select statement as a column

sql

Solution

This depends upon your SQL implementation.

SQL is declarative and it is up to the optimiser to construct a physical plan from the logical specification. SQL Server can decorrelate this sub query and turn into an `OUTER JOIN`

CREATE TABLE A(id INT PRIMARY KEY, col1 INT, col2 INT, col3 INT)

CREATE TABLE B(aid INT)

CREATE CLUSTERED INDEX ix ON B(aid)

/*Fool optimiser into thinking tables aren't empty*/
update statistics A with rowcount = 1000000, pagecount = 100000

update statistics B with rowcount = 1000000, pagecount = 100000

SELECT col1,
       col2,
       col3,
       (SELECT count( B.aid)
        FROM   B
        WHERE  B.aid = A.id) AS col4
FROM   A 

DROP TABLE A, B

Gives plan

Which is basically the same as

SELECT col1,
       col2,
       col3,
       Cnt
FROM   (SELECT COUNT(aid) AS Cnt,
               aid
        FROM   B
        GROUP  BY aid) T
       RIGHT OUTER JOIN A
         ON A.id = T.aid

The sub query is logically represented as a `RIGHT OUTER JOIN` with `MERGE JOIN` as the physical implementation. The merge join processes each input once rather than the row by row behaviour of a nested loops join.

Problem

I have a sql query, ``` SELECT col1, col2, col3, ( SELECT COUNT(id) FROM B WHERE B.aid = A.id ) AS col4 FROM A ``` What is the performance impact of having a select as a column? Will that statement be executed for every row that is returned? I am really just interested in the performance of this query. I know there are other ways getting the count can be accomplished. But in this case I am only trying to understand how sql works with an inline select. Apologies if this question is a dupe, I have looked through stackoverflow and I have not been able to find this anywhere.

Original source