Questions tagged [exists]
The exists tag has no usage guidance.
34
questions
1
vote
1answer
28 views
Sql Exists clause
select *
from product
where productname in (select productname
from product
where ProductQty = 89)
select *
from product
where exists (select ...
1
vote
1answer
32 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 ...
0
votes
3answers
48 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 ...
1
vote
1answer
21 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].[...
0
votes
1answer
31 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`
...
1
vote
2answers
70 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....
0
votes
1answer
56 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 have more than 3 milions rows.
There some rows having same values in the first_origin_id, delivery_id fields.
If there're records with same ...
0
votes
1answer
50 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 ...
0
votes
0answers
46 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 ...
0
votes
3answers
6k 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 ...
0
votes
1answer
57 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 = '...
3
votes
1answer
5k 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....
1
vote
4answers
118 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
...
2
votes
0answers
84 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 ...
11
votes
1answer
1k 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 ...
0
votes
2answers
760 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....
4
votes
1answer
276 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 ...
0
votes
1answer
81 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 ...
16
votes
3answers
4k 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)
...
0
votes
1answer
924 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.
0
votes
1answer
32 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 ...
0
votes
2answers
57 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
...
3
votes
1answer
4k 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, ...
34
votes
2answers
3k 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, ...
66
votes
4answers
109k 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
...
0
votes
1answer
99 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 ...
2
votes
1answer
560 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 ...
3
votes
1answer
94 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 ...
1
vote
1answer
1k 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 ...
0
votes
1answer
661 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 ...
1
vote
1answer
418 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 ...
0
votes
1answer
2k views
How can I create a bool column for a view based on the result of a statement?
I'm using PostgreSQL as my database.
CREATE TABLE groups (
id SERIAL NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id),
UNIQUE (name)
);
CREATE TABLE users (
id ...
0
votes
1answer
11k 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 ...
1
vote
1answer
493 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 ...
