Skip to main content
Share Your Experience: Take the 2024 Developer Survey
58 votes
Accepted

Why does this query work?

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution. When the query is parsed, it is first checked whether table_b has a name column. ...
ypercubeᵀᴹ's user avatar
57 votes
Accepted

Using column alias in a WHERE clause doesn't work

The manual clarifies here: An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the ...
Erwin Brandstetter's user avatar
17 votes
Accepted

Poor performing subquery with date comparisons

With the index definition that you have for IDX_my_nme, SQL Server will be able to seek using the ActionDate column but not with the Address column. The index contains all of the columns needed to ...
Joe Obbish's user avatar
  • 32.2k
14 votes
Accepted

How can SQL Delete using a sub query

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 ...
ypercubeᵀᴹ's user avatar
13 votes
Accepted

How to get all records from one table and see if they are used in another without a sub query

You can use a LEFT JOIN to get the same results. Note how the WHERE condition of the subquery was moved to the ON clause of the join: SELECT uc.UserContractId, uc.UserContractName, oc....
ypercubeᵀᴹ's user avatar
13 votes
Accepted

Why is SQL Server spliting it's (JSON) response into multiple rows?

After further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks". Sql Server splits result of FOR JSON query into ~2KB chunk,...
joeldesante's user avatar
13 votes
Accepted

Unused columns in WHERE clause

Both queries are ridiculously complicated. Use DELETE FROM "Foo" WHERE "CreatedAt" < current_timestamp;
Laurenz Albe's user avatar
12 votes
Accepted

best way to avoid redundant aggregate functions and/or group by columns

If id is defined as the primary key, you can omit grouping by all the foo columns you want for the output as long as you are grouping by the id. This special case of grouping is in accordance with the ...
Andriy M's user avatar
  • 23k
11 votes
Accepted

Performance of count(*) in subquery

Do you have a reason to believe that they will perform differently? If so, why not test them on your RDBMS and with your data? In general I would say to start with the simplest query, test performance,...
Joe Obbish's user avatar
  • 32.2k
10 votes

Get multiple columns from a select subquery

Inspired by dezso's answer https://dba.stackexchange.com/a/222471/127433 I'm solving the problem in PostgreSQL using arrays, like this: SELECT *, p.name AS name, p.image, p.price, (...
tobi42's user avatar
  • 201
10 votes
Accepted

Outer self join filters vs subqueries

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 ...
ypercubeᵀᴹ's user avatar
10 votes
Accepted

Why does adding ROWNUM to a query improve performance?

Posting the query plan for the two versions of the query (and the exact SQL for the other version just for clarity's sake) would certainly be helpful. I would guess that doing so would prove out the ...
Justin Cave's user avatar
  • 20.3k
10 votes
Accepted

Subquery gives no error for a non-existing column with the same name as in the outer query

I run this query: select * from t1 where c1 in (select c1 from t2); The above query should give an error as c1 is not present in t2. Instead, it returns all the rows from t1. No, the query should ...
ypercubeᵀᴹ's user avatar
9 votes

user parent table id in nested subquery

MySQL forbids referencing outer-level columns deeper than one level of nesting. Your query, however, is referencing users.id three levels deep. What you need, therefore, is to rewrite the correlated ...
Andriy M's user avatar
  • 23k
9 votes
Accepted

ERROR: subquery must return only one column

All elements of an array must have the same type; when constructing an array with a subquery, the simplest way to enforce this is to demand that the query returns exactly one column. But you can make ...
CL.'s user avatar
  • 5,233
8 votes
Accepted

Passing a value to a nested subquery

To my knowledge, this limitation pertains to SQL specifically in MySQL. All the other SQL products that I am aware of do not have it. There does not appear to be a solution to this problem in general:...
Andriy M's user avatar
  • 23k
8 votes

MYSQL Subquery in columns of select statement

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 ...
a_vlad's user avatar
  • 3,655
8 votes
Accepted

Should i use subquery to limit table before a join?

The answer to your question depends on the specific database and version you are using. In any case most current databases will optimize the query and end-up having the same execution plan for all ...
joanolo's user avatar
  • 13.5k
8 votes
Accepted

Join is causing other column data to change?

The odds are that your problem is that, when you add in the snacks, you have more than one table that returns more than one row for the columns in your GROUP BY statement - probably Customer_Snacks ...
RDFozz's user avatar
  • 11.6k
8 votes
Accepted

How to combine ORDER BY and LIMIT with an aggregate function?

I am applying order by p.name desc in two places ... Is there a way to avoid that? Yes. Aggregate with an ARRAY constructor in the lateral subquery directly: SELECT c.identifier, p.persons FROM ...
Erwin Brandstetter's user avatar
8 votes

Subquery gives no error for a non-existing column with the same name as in the outer query

It is actualy not an error. You are really referencing the column from outer table. Because of this "feature" you are able to write correlated subqueries and reference the fields from outer query in ...
Loki's user avatar
  • 116
8 votes
Accepted

Limit left join for multiple rows

What version of MySQL are you using? MySQL 8 supports LATERAL which you need to be able to refer to i from the derived table: SELECT i.*, si.child, si.parent_id FROM items as i LEFT JOIN LATERAL (...
Lennart - Slava Ukraini's user avatar
8 votes
Accepted

Replace long GROUP BY list with a subquery

If you change the left join with dbo.Haul to a subquery, it will calculate these distinct values of ID_DestinationAddress (Stream Aggregate) and Count them (Compute scalar) directly after getting the ...
Randi Vertongen's user avatar
7 votes
Accepted

Correlated subquery to find total larger than state average

You can do it like this: select * from dbo.Invoice I1 join dbo.Vendors V1 on V1.VendorID = I1.VendorID where I1.InvoiceTotal > ( SELECT Avg(I2.InvoiceTotal) ...
James Z's user avatar
  • 2,219
7 votes

Why does this query work?

Because Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/...
Serg's user avatar
  • 1,266
7 votes

JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

use json_query, SELECT @@SERVERNAME AS [Servername], json_query(( SELECT [Name], [Recovery_Model_Desc] FROM sys.databases WHERE name in ('master', 'model', 'msdb') FOR JSON PATH, ...
Roshan Vishva's user avatar
7 votes
Accepted

conditional subquery

There are three important parts to reading query plans here, Did it run. If so, How many times? Was it correlated? Sample Data You didn't provide any sample data, so let's create some. CREATE ...
Evan Carroll's user avatar
  • 63.5k
7 votes

how to add loop in date column in sql through query?

You should always have a calendar table to help solve this kind of problem (and you should try to avoid thinking about anything in SQL Server as a "loop" - it is optimized to work on sets). -- this ...
Aaron Bertrand's user avatar
7 votes
Accepted

multiple insert queries after a set of with's

You need to put the INSERT INTO "name-job" into another CTE. You could also put the last insert into "name-city" into a CTE as well, and add a SELECT to get all the inserted ids (and anything else you ...
ypercubeᵀᴹ's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible