Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help in SQL Query

 
Sagar Rohankar
Ranch Hand
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ranchers,

There are two tables tableA and tableB and I need a complement of tableA with tableB , I mean, I want to return all the rows which are absents in tableB, like




Here "tableA COMPLEMENT tableB" results into following rows,



My first try,

Its working , but I'm not sure whether its good or not !
So any alternatives??
 
Jan Cumps
Bartender
Posts: 2584
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are close. Better not concatenate. That is messy.

There are several options:



or, in oracle:
 
Sagar Rohankar
Ranch Hand
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, thanks, we are concentrating more on first option as we are using MS SQL SERVER.
 
Rahul Babbar
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think operations like "Union", "Minus", "Intersect" are standard Relational Operations that all databases should adhere to.
not sure why SQL Server doesn't support this.
 
Sagar Rohankar
Ranch Hand
Posts: 2906
1
Java Spring Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rahul Babbar wrote:I think operations like "Union", "Minus", "Intersect" are standard Relational Operations that all databases should adhere to.
not sure why SQL Server doesn't support this.


Yes, we checked out this basic set operation first, But SQL SERVER dosen't supports MINUS (Yeh, Its support UNION and INTERSECTION). Actually there in no keyword like, minus. And interestingly doesn't have CONCAT operator (Query is constructed on MySQL first) too.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34095
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some database use an alternate keyword for "minus." For example, db2 uses "except." I did check online and SQL Server doesn't have one. Which means this post isn't so much for you as it is for the db2 user who stumbles across this thread.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic