Questions tagged [except]
The usage and semantics of the EXCEPT logical operation
46
questions
4
votes
1
answer
146
views
Query for records from T1 NOT in junction table T2
I have a table containing the names and filesystem locations of C source-files in a table, a list of relevant categories for the programming principals demonstrated by the source files in a second ...
- 143
0
votes
1
answer
79
views
How to handle an expected exception in base postgres
I want to be able to write a .sql script that will carry out an action which fails, and the script should only report failure if the action doesn't fail.
For example, given initial table:
create table ...
- 276
0
votes
1
answer
489
views
how to run trigger on table on postgresql except one column
I'm using postgresql 13/14.
lets say I have a table with column A,B,C,D,E
I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for ...
- 1
0
votes
0
answers
24
views
Remove tables intersection [duplicate]
Given two columns, t1 with distinct values, but a superset of t2, how is it possible to operate over t1 to subtract the elements of t2 of t1? I utilize MySQL 5.7
CREATE TABLE IF NOT EXISTS t1 (
id_ ...
- 117
0
votes
1
answer
883
views
Except command doesn't work in SQL [closed]
I am trying to make an except in SQL but it doesnt exclude the data even though they are identical.
SELECT A.payment_id,
SUM(A.importe_trx) monto_total
FROM [dbo].[ABONOS]...
- 1
9
votes
2
answers
581
views
EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan
The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT.
What is the purpose of the projection? e.g. Is there a different EXCEPT ...
- 869
0
votes
1
answer
125
views
Selecting difference between two tables returns different number of results than counts of same two tables
I'm using SQL Server and when I run
SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2
it returns 1610 entries. When I query
SELECT COUNT(*) FROM TABLE1
SELECT COUNT(*) FROM TABLE2
I get 6399 and ...
- 125
2
votes
2
answers
2k
views
WITH ... AS statement not working as expected
I get this error:
It arises when I try to execute the following query:
with expensive_service as (
select s1.*
from service s1, service s2
where s1.price > s2.price
)
select *
from ...
- 23
1
vote
1
answer
133
views
SQL Except Statement and Add in One more column
How do I conduct a Except statement and Add in one more column?
Three tables with the exact same structure. (CustomerTransaction1,CustomerTransaction2, CustomerTransactionDiff)
I want the LoadDate ...
user162241
11
votes
1
answer
1k
views
What is the algorithm behind the EXCEPT operator?
What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?
David Lozinksi ran a study, SQL: Fastest way to ...
user162241
0
votes
1
answer
874
views
EXCEPT operator query to differentiate between new and changed rows
I am using the EXCEPT Operator to compare data between two tables (as part of a final import I want to compare "today's data" with "yesterday's data" and only finally import new and changed rows.)
...
- 3
6
votes
2
answers
558
views
Unusual column comparisons and query performance
We have some consultants working on expanding an inhouse data warehouse. I was doing a code review and ran across this pattern in all of the load procs:
MERGE [EDHub].[Customer].[Class] AS ...
- 267
3
votes
2
answers
687
views
Using T-SQL, is it possible to a split a result set or table and then insert into two different temp tables?
I have one table that holds payment data. I'd like to insert orders that ended in a declined state into #tmp_Declined. Then I'd like to insert payments that were originally declined but then ...
- 31
2
votes
2
answers
3k
views
Difference between two tables
I am trying to pull the differences between two tables, each in a different database. I thought I could just do a full outer join but I am getting back too many results.
The T-SQL below returns 167 ...
- 43
6
votes
1
answer
7k
views
T-SQL MINUS operator
These are the tables I have created and inserted values in it:
CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
Customer_Name VARCHAR(30) NOT NULL
)
CREATE TABLE DVD
(DVD_No ...
- 155
0
votes
1
answer
577
views
Using EXCEPT before WITH RECURSIVE in SQLite 3
Is there anyway to do this? I'm trying to do something like this:
SELECT x
FROM table
EXCEPT
WITH RECURSIVE ...();
- 13
4
votes
1
answer
2k
views
Find rows matching only values from a specific list with SQL
I have a table like this:
Id Status
-- -----------
1 Closed
1 In progress
2 Rejected
3 Cancelled
4 Rejected
4 Closed
5 In progress
I want to show ...
- 41
0
votes
1
answer
41
views
Inserting two tables together with no Id or UNIQUE anything
Let's assume we have two tables:
table1, retaining the following data:
Name Height Age
---- ------ ---
Mary 1.60 18
Mary 1.62 19
Mary 1.68 20
John 1.80 17
John 1.85 18
John 1.86 19
...
- 3
1
vote
2
answers
977
views
Does the EXIST clause only return distinct rows by its nature?
I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database.
The first query uses an EXISTS clause with a subquery:
...
- 311
5
votes
1
answer
1k
views
Sub-select takes "ages" - EXCEPT is much faster
Script to create the tables
DROP TABLE IF EXISTS history;
CREATE TABLE history (
id integer NOT NULL,
ticket_id integer NOT NULL);
ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY ...
- 1,421
9
votes
1
answer
3k
views
What is most efficient way to compare two large result sets in SQL Server 2012
Current advice for the most efficient way to compare two large result/row sets seems to be to use the EXCEPT operator. This self contained SQL script below gets very inefficient as row sizes increase (...
- 93
6
votes
3
answers
7k
views
Contained DB Collation error
When changing a database to partially contained I am getting the following error:
Cannot resolve the collation conflict between "Latin1_General_CI_AS"
and "Latin1_General_100_CI_AS_KS_WS_SC" in ...
- 1,539
1
vote
1
answer
2k
views
Find unmatched rows between two tables dynamically
I have a function here that is supposed to take two tables as arguments and check if they are the same.
create or replace function testing.equal_tables(
varchar,
varchar)
returns void as
$...
- 148
0
votes
2
answers
94
views
How to JOIN each row ON NOT IN ( SELECT ... )?
Say I have a table of users, songs, and songs played.
users
id: int
songs
id: int
songs_played
user_id: int
song_id: int
If I want to, for each user, find a song that has not yet been played. How ...
- 1
6
votes
2
answers
2k
views
List of all missing order numbers that are not in the table
Our workplace has started to scan all documents rather than manually keep them in archives etc.
On average we are scanning 1,000 documents per time (this happens about 30 times per day).
Each time ...
- 311
1
vote
2
answers
6k
views
How to use except but excluding a column from the except operator comparison -psql? [closed]
I want to compare two sets of columns but exclude one column from the comparison as it will never be in the second table and I need it on my result.
So, explaining:
columns for both tables:
ID (...
3
votes
1
answer
853
views
How to find missing data from one table that is a join of two other tables
I have two tables, stock and location, with a third table that retains the level of stock at each location, stock_loc_info.
Each stock item should have a row for each location.
What SQL query ...
- 33
18
votes
3
answers
41k
views
EXCEPT operator vs NOT IN
The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?
Does it do the same? I would like a simple explanation with an example.
- 1,495
0
votes
1
answer
76
views
Move/Copy unique rows from TableA to TableB, if they are not in TableB
I'm required to move every unique row from TableA to TableB, if the unique row from TableA is not already in TableB.
Both tables look the same, they don't have any keys, but there's a combination of ...
- 267
1
vote
2
answers
797
views
ACID violation in Postgres 9.1?
I am using a Postgres DB to implement a scheduling of jobs for a large number of computers/processes. To make the story short, every job has its id, all scheduling is implemented with three tabes: all ...
- 868
4
votes
4
answers
1k
views
Self Join? Or am I wrong?
We have a table that we use to store customers and items. We need to be able to find customers that have bought one item and have not bought another item:
customer item
Sally 1
Sally ...
- 41
1
vote
1
answer
3k
views
How to compare data between staging and operational tables?
I just want to ask how to compare two database tables, staging vs. operational tables.
After we did the transformation and convert it to correct datatype like convert flag column (Y/N) nvarchar to ...
- 245
0
votes
1
answer
4k
views
ORA-00907: missing right parenthesis on query with subqueries and EXCEPT operator
I am getting
ORA-00907: missing right parenthesis
for the following query:
SELECT PEOPLE.NAME FROM
CLIENTS K INNER JOIN PEOPLE ON K.ID_L = PEOPLE.ID_L
WHERE NOT EXISTS
(
(SELECT DISTINCT ARTIST....
- 3
12
votes
2
answers
21k
views
An efficient way to compare two large data sets in SQL
Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations.
The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
- 127
14
votes
1
answer
407
views
Should NOT IN be avoided?
Among some SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (...
- 3,145
12
votes
3
answers
38k
views
Identifying which values do NOT match a table row
I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.
To better explain, here's what I would do now, to check which IDs of the list "...
- 765
15
votes
4
answers
28k
views
Quick way to validate two tables against each other
We're doing an ETL process. When all is said and done there are a bunch of tables that should be identical. What is the quickest way to verify that those tables (on two different servers) are in ...
- 3,386
3
votes
4
answers
20k
views
How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?
I have two databases - Database1 and Database2.
Both databases contain a table that has similar structure, exemplified as follows:
====================================================================...
- 2,479
2
votes
1
answer
430
views
Selecting values that are not associated with one side of a many to many relationship
I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category.
...
- 227
2
votes
2
answers
3k
views
COUNT with set DIFFERENCE and UNION
Fairly simple question that I can't seem to find an answer to - I'm working with unions and differences, and I would like to perform a COUNT on the results. Currently I'm having to pipe out to a file ...
- 1,135
3
votes
3
answers
6k
views
Best way to work around NOT IN NULL issue?
There are plenty of explanations of why NOT IN NULL doesn't work, but I didn't see any solutions. (recent question here: Why does NOT IN with a set containing NULL always return FALSE/NULL?)
I have 6 ...
- 1,516
3
votes
3
answers
2k
views
Identifying rows which don't match a master row
I'm comparing a bunch of tables from different databases on different servers to a Master record. I need to know which servers, identified by locationID, have the non-matching rows because they might ...
- 2,063
20
votes
4
answers
116k
views
Easily show rows that are different between two tables or queries
Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the ...
- 4,295
16
votes
5
answers
44k
views
SQL Server Linked Server performance: Why are remote queries so expensive?
I have two database servers, connected via Linked Servers. Both are SQL Server 2008R2 databases, and the linked server connection is made via a regular "SQL Server" link, using the current login's ...
- 547
33
votes
2
answers
11k
views
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) ...
- 2,139
8
votes
2
answers
7k
views
How do I replace this where clause with a join?
Typically when I see SQL that uses something like:
select * from employees where epmloyeeTypeId in (select id from type where name = 'emp')
I replace the where with this:
select e.* from employees ...
- 2,012