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

Questions tagged [exists]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
76 views

How does different forms of EXISTS() affect performance in MS Sql Server?

I've been told that when checking for the existence of a row in a table, this will work: EXISTS(SELECT 1 FROM....) but that a "better" way in regards to speed performance of the query would ...
Gharbad The Weak's user avatar
0 votes
1 answer
103 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 ...
J. Mini's user avatar
  • 431
0 votes
2 answers
115 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 ...
Exprove's user avatar
  • 109
0 votes
1 answer
158 views

IF EXISTS doesn't stop errors on RDS Postgres 11.19 instance

Recently one of our RDS Postgres databases was upgraded to version 11.19. Since then, we're receiving errors when running a database migration. I can't replicate this issue locally, it appears to be ...
Ben Hitchcock's user avatar
0 votes
1 answer
52 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 ...
Himanshuman's user avatar
0 votes
1 answer
637 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 ...
Paul Soare's user avatar
0 votes
2 answers
48 views

I have a database with a player, tournament, and registration table. I need to query all players that played in 2 types (attributes) of tournaments

I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both This ...
zaria palmer's user avatar
0 votes
1 answer
1k 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 ...
THpubs's user avatar
  • 153
5 votes
2 answers
444 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, ...
Joe's user avatar
  • 1,645
1 vote
1 answer
562 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 ...
hap76's user avatar
  • 15
5 votes
1 answer
835 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 ...
scorpp's user avatar
  • 163
1 vote
1 answer
456 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 ...
wimg's user avatar
  • 107
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 ...
William Oliveira's user avatar
0 votes
1 answer
44 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....
Kim's user avatar
  • 11
-1 votes
2 answers
127 views

MYSQL get rows not axisting in other table

I have two tables with 1 identical data column for comparison. One table "species" is a wide list of species and each data row has one unique id called "suid" add. to its primary ...
BigMac's user avatar
  • 1
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 ...
Joe  Pineda's user avatar
3 votes
3 answers
16k 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)...
a_shie's user avatar
  • 31
1 vote
2 answers
502 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 ---------+---------+--------...
Taimoor Ahmad's user avatar
0 votes
2 answers
3k 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 ...
Matthew Moisen's user avatar
2 votes
1 answer
335 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 ...
Archmede's user avatar
  • 165
0 votes
1 answer
76 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 ...
Tom L.'s user avatar
  • 103
0 votes
4 answers
99 views

How to visualize joins between inner queries to their respective outer queries?

I seem to always have trouble visualizing what the meaning is when an inner query is joined to it's outer query. Here's an example: SELECT NM.MovieId, NM.MovieTitle FROM NinjaMovies AS NM WHERE ...
J.D.'s user avatar
  • 37.8k
3 votes
1 answer
54 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 ...
Rod-Miller's user avatar
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 ...
John Bend Bend's user avatar
4 votes
2 answers
2k 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 ...
BBaggins's user avatar
1 vote
1 answer
279 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 ...
John D's user avatar
  • 11
-1 votes
1 answer
37 views

How to check existence of a value in between two columns of the same table

I have a table bills having columns id, starting_bl_no, ending_bl_no. How can I check a new bill number input is already exist in the table? id starting_bl_no ending_bl_no 1 100 ...
Navaneet Raman Bhaskar's user avatar
1 vote
1 answer
77 views

Sql Exists clause

select * from product where productname in (select productname from product where ProductQty = 89) select * from product where exists (select ...
user11497433's user avatar
1 vote
1 answer
161 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 ...
1990rk4's user avatar
  • 111
0 votes
3 answers
135 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 ...
user avatar
1 vote
1 answer
158 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].[...
levi Clouser's user avatar
2 votes
1 answer
341 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` ...
Js Lim's user avatar
  • 121
1 vote
2 answers
106 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....
andriij's user avatar
  • 13
0 votes
1 answer
574 views

Select only those records that have same multiple values for a particular column if it's existing?

Below is an example of my pricing_supports table with more than 3 millions rows. There some rows having same values in the first_origin_id, delivery_id fields. If there are records with same ...
Dương Khoa's user avatar
0 votes
1 answer
296 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 ...
edoyak's user avatar
  • 1
0 votes
0 answers
69 views

Executing IF Exists condition

I have a sproc that scripts out permissions and in the process, I add a check to see if the object exists before dumping the results into a table. Then, I have another re-assign permissions sproc that ...
Laurent's user avatar
  • 73
9 votes
3 answers
58k 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 ...
Daniel Santos's user avatar
0 votes
1 answer
216 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 = '...
michal's user avatar
  • 105
10 votes
1 answer
31k 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....
Ian23's user avatar
  • 103
1 vote
4 answers
209 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 ...
Ironman454's user avatar
3 votes
0 answers
180 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 ...
Déjà vu's user avatar
  • 473
11 votes
1 answer
5k 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 ...
Evan Carroll's user avatar
  • 63.5k
0 votes
2 answers
4k views

Select Data from two tables ONLY IF ID on both tables are SAME

I need to select returns result SELECT wl.id FROM wishlist AS wl LEFT JOIN wishlist_products AS wp ON wl.id = wp.wishlist_id Only IF ALL of wp.product_id EXISTS in wishlist_products_bought....
andrei_sharov's user avatar
5 votes
1 answer
544 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 ...
miledba's user avatar
  • 105
0 votes
1 answer
699 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 ...
Alex Willison's user avatar
21 votes
3 answers
14k 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) ...
TheGameiswar's user avatar
  • 2,989
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.
Darko Milic's user avatar
0 votes
1 answer
37 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 ...
Jamie's user avatar
  • 3
0 votes
2 answers
103 views

How to check if a record is linked with two or more values in a left join

In MySQL, I have a table of products connected with a different table with categories. I often show the list by making a LEFT JOIN and GROUPING the results: e.g. LEFT JOIN gives me: Name | Category ...
mesqueeb's user avatar
  • 189
5 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, ...
Bjørn Fridal's user avatar