MYSQL Subquery in columns of select statement

MySQLsubquery

How are subqueries in the column field of select (projection) paired with the result of the main query? in the form:

 SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

Is the subquery executed once per row, from the output of SELECT id,email FROM Users,and thus, one should use LIMIT 1 on the subquery (since only 1 row from the subquery can be paired with a row from the main query), or does the subquery run once, and then each result is paired with the corresponding row from SELECT id,email FROM Users, much like the equivalent join: SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

Best Answer

in ideal situation, when Names.id = Users.id return only 1 record, both queries the same.

The difference when it not true.

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id) as name
 FROM Users

will stop work and return error, so You will need add LIMIT class

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY something LIMIT 1) as name
 FROM Users

at the same time query

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id

continue work without errors, this query return all rows from Names related to Users

In some other cases when You expect only 1 Name, You will need add GROUP BY conditions

SELECT id,email,name FROM Users JOIN Names ON Users.id=Names.id GROUP BY Users.id

But this condition could return unpredicted name from Names (and it not 100% legal construction for SQL), and You again would need add 1 more level of JOIN with derived tables, and some time it could be ugly construction,

so, You always can compare what form of query more correct for selected case, simple example:

SELECT t1.id,t1.email,t2.name FROM Users t1 JOIN 
(SELECT id, name FROM Names n1 INNER JOIN 
(SELECT MAX(dateregistered) as dateregistered, id FROM Names GROUP BY id) n2 ON n1.id=n2.id AND n1.dateregistered=n2.dateregistered) t2
ON t1.id = t2.id

will return same result as:

SELECT id,email,(SELECT name From Names WHERE Names.id=Users.id ORDER BY dateregistered DESC LIMIT 1) as name
FROM Users

Add: Example with Names look like not realistic, but real situation - when You need request not name which is really 1 per person, but actual postal address for client with 10 years history. He can have 20 addresses, and You need return most resent

Related Solutions

Sql-server – SQL Subquery , One to Multiple rows

You will have to include DG in your grouping...

Select Consignment_id
       ,Orders_boxsize
       ,Orders_boxweight
       ,Orders_boxlength
       ,Orders_boxwidth
       ,Orders_boxdepth
       ,Order_dispatchment_id
       ,SUM(Component_weight) as Component_weight
       ,SUM(_ic) as _ic
       ,DG
from
(       
SELECT dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id,
       SUM(
           ISNULL(dbo.COMPONENT.Component_weight, 0) * ISNULL(dbo.ORDER_ALLOCATE.Order_allocate_qty, 0)
       ) AS Component_weight,
       COUNT_BIG(*) AS _ic
,(
               SELECT CASE 
                           WHEN EXISTS (
                                    SELECT *
                                    FROM   dbo.ORDER_ITEMS
                                    WHERE  Component_id IN (1111, 1111)
                                           AND Orders_id = dbo.ORDERS.Orders_id
                                ) THEN CAST(1 AS BIT)
                           ELSE CAST(0 AS BIT)
                      END
           ) AS DG       
FROM   dbo.Articles_in_Consignment
       INNER JOIN dbo.ORDER_D
            ON  dbo.Articles_in_Consignment.Order_dispatchment_id = dbo.ORDER_D.Order_dispatchment_id
       INNER JOIN dbo.ORDERS
            ON  dbo.ORDER_D.Order_dispatchment_id = dbo.ORDERS.Order_dispatchment_id
       INNER JOIN dbo.ORDER_ITEMS
            ON  dbo.ORDERS.Orders_id = dbo.ORDER_ITEMS.Orders_id
       INNER JOIN dbo.ORDER_ALLOCATE
            ON  dbo.ORDER_ITEMS.Order_items_id = dbo.ORDER_ALLOCATE.Order_items_id
       INNER JOIN dbo.STOCKIN
            ON  dbo.ORDER_ALLOCATE.Stockin_id = dbo.STOCKIN.Stockin_id
       INNER JOIN dbo.COMPONENT
            ON  dbo.STOCKIN.Component_id = dbo.COMPONENT.Component_id
WHERE  (dbo.ORDERS.Cold = 'No')
GROUP BY
       dbo.Articles_in_Consignment.Consignment_id,
       dbo.ORDER_D.Orders_boxsize,
       dbo.ORDER_D.Orders_boxweight,
       dbo.ORDER_D.Orders_boxlength,
       dbo.ORDER_D.Orders_boxwidth,
       dbo.ORDER_D.Orders_boxdepth,
       dbo.ORDER_D.Order_dispatchment_id
) a
Group by
Consignment_id
,Orders_boxsize
,Orders_boxweight
,Orders_boxlength
,Orders_boxwidth
,Orders_boxdepth
,Order_dispatchment_id
,DG

MySQL – How to Iterate Subquery in Aggregate Select

(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost Is the answer, as provided by ypercube.

I was victim to order of operations--MySQL was taking the sum of gallons pumped, then multiplying that against the first/last result found from the gas prices sub query. As Michael quoted and noted:

"The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So to get what I was after, the sum of each entry multiplied against the price of gas during that week, you simply wrap that exact expression in the comfortable embrace of a sum().

Best Answer

Related Solutions

Sql-server – SQL Subquery , One to Multiple rows

MySQL – How to Iterate Subquery in Aggregate Select

Related Question