Questions tagged [except]

The usage and semantics of the EXCEPT logical operation

Filter by
Sorted by
Tagged with
1
vote
1answer
40 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 ...
10
votes
1answer
308 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 ...
0
votes
1answer
92 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.) ...
6
votes
2answers
408 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 ...
3
votes
2answers
147 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 ...
2
votes
2answers
2k 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 ...
6
votes
1answer
1k 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 ...
0
votes
1answer
456 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 ...();
4
votes
1answer
88 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 ...
0
votes
1answer
40 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 ...
1
vote
2answers
72 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: ...
5
votes
1answer
272 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 ...
9
votes
1answer
951 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 (...
4
votes
3answers
3k 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
vote
1answer
874 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 $...
0
votes
2answers
85 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 ...
6
votes
2answers
872 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 ...
0
votes
2answers
611 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 (...
2
votes
1answer
305 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 ...
18
votes
3answers
31k 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.
0
votes
1answer
65 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 ...
1
vote
2answers
631 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 ...
4
votes
4answers
962 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 ...
1
vote
1answer
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 ...
0
votes
1answer
3k 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....
12
votes
2answers
15k 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 ...
14
votes
1answer
368 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. (...
9
votes
3answers
20k 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 "...
12
votes
4answers
20k 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 ...
2
votes
3answers
14k 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
votes
1answer
177 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. ...
1
vote
2answers
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 ...
3
votes
3answers
5k 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 ...
3
votes
3answers
1k 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 ...
19
votes
4answers
98k 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 ...
13
votes
5answers
36k 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 ...
33
votes
2answers
9k 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) ) ...
8
votes
2answers
4k 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 ...