Questions tagged [exists]
The exists tag has no usage guidance.
60
questions
121
votes
5
answers
302k
views
Best practice between using LEFT JOIN or NOT EXISTS
Is there a best practice between using a LEFT JOIN or a NOT EXISTS format?
What is benefit to using one over the other?
If none, which should be preferred?
SELECT *
FROM tableA A
LEFT JOIN tableB B
...
- 1,385
40
votes
2
answers
6k
views
IF EXISTS taking longer than embedded select statement
When I run the following code it takes 22.5 minutes and does 106million reads. However, if I run just the inner select statement by itself it only takes 15 seconds and does 264k reads. As a side note, ...
- 1,761
20
votes
3
answers
13k
views
Wrapping query in IF EXISTS makes it very slow
I have the below query :
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
...
- 2,949
11
votes
1
answer
4k
views
Does the SQL Spec require a GROUP BY in EXISTS ()
Microsoft currently permits this syntax.
SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
SELECT *
FROM ( VALUES (1),(1) )
AS t(x)
WHERE g.x = t.x
HAVING count(*) > 1
);
Notice ...
- 62.1k
10
votes
1
answer
28k
views
pg_restore ERROR: "Relation does not exist" and creating new database
I have made a backup of my specific tables that I want to restore into a new database using:
call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public....
- 103
9
votes
3
answers
57k
views
How to check if all elements of an array exists in a table?
Lets take an array of ids of size n in the application level. for instance [132,3425,13,13,... 392] where n is bigger than 100k entries. Some of this entries are repeated.
I need to check if all of ...
- 293
5
votes
1
answer
687
views
EXISTS() vs EXISTS() = TRUE in Postgres
Faced weird behaviour with EXISTS (also applies for NOT EXISTS) generating different execution plans for
WHERE EXISTS(...)
EXPLAIN ANALYZE
SELECT * FROM books
WHERE EXISTS (SELECT 1 FROM authors WHERE ...
- 163
5
votes
2
answers
393
views
Is this DELETE ... WHERE EXISTS query plan O(n*2) or O(n^2)?
I'm trying to perform a common task, deleting duplicates from a table with the aim of adding a unique constraint.
CREATE TABLE IF NOT EXISTS item_identifier (
pk BIGSERIAL PRIMARY KEY,
...
- 1,631
5
votes
1
answer
530
views
SQL Server OR operator causing massive loop join
The query below is very slow (running over a minute) and I have narrowed down the issue to the OR operator (...OR (EXISTS (SELECT...).
I used live execution to verify that there is a nested loop join ...
- 105
4
votes
2
answers
1k
views
Which Plan is Better? WHERE EXISTS Instead of INNER JOIN
Did a switch to a WHERE EXISTS in temp table (Object13 in the plans) from an INNER JOIN to said temp table in final select in procedure.
Logical reads plummeted, but plan cost and memory ...
- 41
4
votes
2
answers
16k
views
Howto combine UNION with EXISTS?
I have a query that looks like this:
SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note
Which works fine. My trouble start when I try to add an EXIST to it:
SELECT PubKey, ...
- 55
3
votes
3
answers
13k
views
How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?
The below is a valid TSQL statement. But I want to understand how SQL Server processes it.
DELETE A
FROM table1 WITH (NOLOCK)
WHERE
EXISTS
(
SELECT 1
FROM
table2 B WITH (NOLOCK)...
- 31
3
votes
1
answer
53
views
Help filtering out records in MYSQL
I hope that someone can help me.
I need to search to filter out values in a database. The database has about 14 000 records of Koalas that have come into care.
Each Koala has their own name with a ...
- 33
3
votes
1
answer
116
views
How does 'exists' affect output
I previously asked a question about how to organize a long search query. I have taken it apart. I am presently attempting to understand the function of some of its parts. Specifically, I am ...
- 415
3
votes
1
answer
2k
views
Full text search on two tsvector columns
I'm trying to do a full text search across two columns of two separate tables in PostgreSQL 9.4.2. The tables and columns are:
article.article_title
keyword.keyword
Where there is a many-to-many ...
- 145
3
votes
0
answers
172
views
"FOR UPDATE" in an "EXISTS SELECT" statement
Is there any potential problem in using (in a transaction)
SELECT EXISTS( SELECT * FROM mytable WHERE id=36 FOR UPDATE )
i.e. having SELECT ... FOR UPDATE within an EXISTS?
MySQL does certainly ...
- 463
2
votes
2
answers
2k
views
Postgres slow exists subquery
I have a queue system that has two tables, jobs and jobs_queues.
jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
2
votes
1
answer
281
views
Caveats of OUTER JOIN on nested JSON value
I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
- 165
1
vote
3
answers
2k
views
NOT EXISTS with two subquery fields that match 2 fields in main query
Background: Two different document types in a document management system.
Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date.
What we're looking for:
Doc Type A docs that don't have a ...
- 13
1
vote
4
answers
207
views
Select number of times 2 specific values show up for one record id
I'm not sure my title above makes sense, so I'll try to clarify. I'm using SQL Server.
I have a table called Orders that looks like this (truncated):
Order_Number | SKU
1 | abc
...
- 11
1
vote
2
answers
3k
views
MS Access: adding column if not exist (or try/catch it)
this is an easy question, but I can't find out the solution...
I have to check if a column exists and add it if the test is negative. How can I do with only sql code?
This is the syntax for SQL ...
- 143
1
vote
1
answer
70
views
Sql Exists clause
select *
from product
where productname in (select productname
from product
where ProductQty = 89)
select *
from product
where exists (select ...
- 65
1
vote
1
answer
572
views
Performance of checking for data in related tables
NB: this question is purely academic / to help improve my understanding of SQL Server performance.
Given a master table which relates to one or more other tables, how would you determine the best ...
- 449
1
vote
2
answers
103
views
Why my query does not return correct data (NORTHWIND)?
select *
from orders o
WHERE EXISTS
(
select 1
from orders o2
where o2.EmployeeID = o.EmployeeID
AND YEAR(o2.OrderDate) = 1997
and MONTH(o2.OrderDate) = 9
AND DAY(o2....
- 13
1
vote
1
answer
1k
views
Determine existence in recursive working table
I have a query that needs to check for existence of each input row type in another table, but it is unnecessary and inefficient to check for existence of subsequent rows of the same row type if one is ...
- 555
1
vote
1
answer
435
views
Looking for an alternative to a CTE that will work as a subquery in IF EXISTS
I have an IF EXISTS 'upsert' running fine by itself in it’s own stored proc. But when I try and use the same statement referencing a CTE, it doesn't recognize the CTE. I see in related post that I'm ...
- 15
1
vote
2
answers
352
views
Select where combined with exists
Table dating_users:
user_id | username
---------+----------
11 | taimoor
13 | jacob
14 | pi
(3 rows)
Table dating_likes:
like_id | user_id | liked_user
---------+---------+--------...
- 115
1
vote
1
answer
4k
views
Unexpected results from MariaDB IF EXISTS ... THEN
I am having unexpected results from MariaDB IF EXISTS... in a stored procedure. I created a minimal example to demonstrate the issue:
PROCEDURE testdb.testSearchResults(IN pParam1 INT, IN pParam2 ...
1
vote
1
answer
271
views
WHERE IN subquery too slow
I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k ...
- 11
1
vote
1
answer
132
views
Exist on table from linked Server giving Error 4104
I am comparing two tables on different Servers and the query I am running looks like this:
Select top 100 *
From [ServerA].[MyDB].[dbo].[Table1]
Where Exists( Select * from [ServerB].[TheirDB].[...
- 123
1
vote
1
answer
253
views
MySQL under what scenario suitable to use WHERE EXISTS
I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.
select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
from `table_2`
...
- 111
1
vote
1
answer
358
views
Inconsistent results with MySQL, query never ends on MariaDB
We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
We have 2 tables + 1 link table in-between, illustrated by this schema :
We run a query whereby we need to fetch the id ...
- 107
1
vote
1
answer
144
views
Weird error on creating table and selecting rows from table
I have db and I am trying to create table called "sales_invoice_grid"
I am having bellow scenarios:
error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it ...
- 111
0
votes
1
answer
630
views
How to determine if a timestamp in one table is between two timestamps in a subquery of another table?
I have two tables - one titled "index" which contains the 'datetime' timestamp with time zone, and one titled "planning" which contains the 'start_time' and 'end_time' timestamps.
Here are the schema ...
- 199
0
votes
1
answer
141
views
Select all from A where attribute exists in B [closed]
I have a question involving relational algebra. Suppose I have two relations A and B, where B has a foreign key that references A.
Using relational algebra, how do I select all tuples from A where ...
- 415
0
votes
1
answer
13k
views
Use if exists in postgresql [closed]
I know about the exists feature, but that does not help me with the given situation. What I want is:
Check if some row exists, and if exists then check for an another row if row exists.
Here's ...
- 789
0
votes
1
answer
89
views
Is WHERE [FOO] IN (SELECT [BAR] FROM [...]) guaranteed to give the same execution plan as the equivalent WHERE EXISTS?
In the past, there was a lot of paranoia around IN. In particular, I remember claims that it can be optimised differently to an equivalent WHERE EXISTS. Is this claim still true? Or is it now ...
- 45
0
votes
1
answer
42
views
Return all archive records when any one record meets criteria
I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September).
The Archive Table
SELECT PTA.PlnndTrnsfrID, PTA....
- 11
0
votes
1
answer
70
views
Optimise XOR search on int list
I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int).
Another large table (TableB) contains the same ID column with some other ...
- 103
0
votes
3
answers
110
views
What order does Oracle's EXIST condition compare the sub-query results?
I know EXIST works better than IN for larger sub-queries because it checks each sub-query record individually and stops once a comparison is found.
Let's say I can order the sub-query in a way to ...
ray_haan
0
votes
1
answer
1k
views
Helpme undestand MDX exists function
After spend almost decade in OLTP world I got the courage to try to build my first CUBE.
I installed VS2012 and created Business Intelligence -> Analysis Services.
I did build Data Source Views, Cube ...
- 2,403
0
votes
2
answers
2k
views
How to check existence in Oracle without scanning the entire table?
Normally, to check existence in Oracle I will do:
SELECT COUNT(1)
FROM foo
WHERE bar = 'baz'
However, if the foo table contains multiple rows where bar='baz', this query needlessly scans through the ...
- 197
0
votes
2
answers
101
views
How to apply JOIN only if there are rows available
Given the following database:
CREATE TABLE "Activity"
(
"id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
"designation" TEXT NOT NULL
);
CREATE ...
- 109
0
votes
1
answer
25
views
Set variable on multiple case within each group
Say I have below table
cis
aid
cid
apid
ntime
buid
flg
jid
30
1208
229067737
1026
9
DDDD400C
0
0
30
1209
229067737
1026
11
DDDD400C
0
0
30
0
229067737
1026
12
DDDD400C
1
100
30
1210
229067737
...
- 197
0
votes
1
answer
446
views
Select rows in Postgresql where condition is fullfiled by all the members
I have a question about a topic that was already discussed:
select rows having condition met for group (without temporary table)
Let's say I have:
ID
Name
Account
Year
1
Jack
0001
1928
2
John
0001
...
0
votes
1
answer
971
views
How to use a JOIN inside a CASE expression?
I have four tables like this:
company:
id
companyContactID
customerID
companyTeamID
1
12
21
54
2
14
12
78
document_associated_company:
id
companyID
documentID
1
2
98
2
1
12
document
id
...
- 153
0
votes
1
answer
291
views
Update + where not exist
once again, I come here for your rescue.
how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of ...
- 1
0
votes
1
answer
207
views
SELECT SUM and remove similar rows
SELECT
SUM(coins.available_supply * hist_all.price) AS market_cap_usd_all
FROM coins
LEFT JOIN hist_all ON coins.id = hist_all.coins_id
WHERE hist_all.price != 0
AND (hist_all.second_coin = '...
- 105
0
votes
1
answer
3k
views
Check if file exists in step in SQL
Need to create step in SQL job which check if file exist because if file exists it needed to be stop but if file not exist to continue with next step.
xp_fileexist not allowed in sql query.
- 454
0
votes
1
answer
35
views
Returning data based on if another column in another table is NULL
I'm trying to generate a list of data which is on completed status but has not been billed. I am able to produce a list of all completed orders however i am unable to remove the ones which have been ...
- 3