New answers tagged subquery
Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try: Where (Select Count(Distinct fld1) From t1 ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3
SELECT * FROM animals WHERE FIND_IN_SET(name,(SELECT @animal_names)); See the FIND_IN_SET documentation
Build a Stored Routine; in it use CONCAT to construct the SELECT with the @variable stitched in; prepare and execute. Alternatively, if you are using an application language such as PHP, do the stitching in that language.
In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist). An easy fix for this is to move that condition to the on clause like this: select x.* from factI as ...
Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and ...
You could do: SELECT * FROM animals WHERE CONCAT(',', @animal_names, ',') LIKE CONCAT('%,\'', name, '\',%'); It won't perform fast, but it'll work.
The variable contains ONE string, but to work properly the subquery in IN() needs to return different items as multiple rows. SELECT * FROM animals WHERE name IN(SELECT @animal_names); is translated to SELECT * FROM animals WHERE name IN(SELECT '\'dog\',\'cat\',\'penguin\',\'lax\',\'whale\',\'ostrich\''); MySQL variable cannot hold table resultset so ...
My MySQL knowledge is rusty (at best), but subqueries like this can normally be replaced by a JOIN and GROUP BY to achieve the desired result. Something like this will get you on your way: SELECT name, qty_ordered, item_id, original_price, discount_percent, price, tax_percent, MAX(CASE WHEN ...
Try the following SQL command: SELECT p.name,p.image,p.price,pss.price,pss.date FROM Product p OUTER APPLY(SELECT TOP(1)* FROM ProductSpecial ps WHERE p.Id = ps.Id ORDER BY ps.priority )as pss
The subquery you have in your code is called a derived table. It's not a base table but a table that "lives" during the time that the query runs. Like views (which are also called viewed tables) - and in recent versions CTEs which is another, 4th way to "define" a table inside a query - they are similar to a table in many ways. You can select from them, you ...
It's easy to see when you look at the query plan. In your case, the plan just contains an additional Segment and Sequence Project operator to handle the row number. This type of operation only works when SQL Server actually can resolve the underlying table. Deleting from subqueries and CTEs is fully supported and very efficient, particularly for removing ...
Top 50 recent answers are included