63
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 ...
- 180k
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. ...
- 98.5k
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 ...
- 32.6k
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....
- 98.5k
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,...
- 311
13
votes
Accepted
Unused columns in WHERE clause
Both queries are ridiculously complicated. Use
DELETE FROM "Foo"
WHERE "CreatedAt" < current_timestamp;
- 54.8k
11
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,
(...
- 211
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,...
- 32.6k
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 ...
- 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 ...
- 98.5k
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 ...
- 23.1k
9
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 ...
- 180k
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 ...
- 5,263
8
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, ...
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:...
- 23.1k
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
...
- 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 ...
- 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 ...
- 11.7k
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 ...
- 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 (...
- 23.6k
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 ...
- 16.5k
8
votes
Need help rewriting view to avoid using subqueries in MySQL 5.1 db
I hope the next code is clear enough
SELECT q.submission_id
, q.attribute_id
, q.response AS is_reporting
, w.response AS email
FROM frm_form_attribute_data AS q
JOIN ...
- 4,566
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/...
- 1,266
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 ...
- 64.2k
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 ...
- 181k
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 ...
- 98.5k
7
votes
Using column alias in a WHERE clause doesn't work
The answer was sort of posted in a comment but I had a similar problem and was directed here, figured I would share as an answer. Your alias should appear in the inner select, not the outer.
select t....
- 171
7
votes
Accepted
Use NEW in FROM clause in Postgres trigger?
NEW and OLDin trigger functions are records - each only defined where applicable. (So no OLD in an INSERT trigger, for instance.) You can perform ROW operations on them. Reference nested columns with ...
- 180k
7
votes
Accepted
MySQL reuse select aliases
The SQL standard does not allow the reuse of the alias here.
However, MySQL uses an extension to the standard that enables you to do something close, namely use a previously defined alias in a ...
- 1,271
Only top scored, non community-wiki answers of a minimum length are eligible