• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

EXISTS and IN execution logic  RSS feed

 
Ranch Hand
Posts: 340
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to understand how the execution takes place when we have EXISTS or IN the SQL queries. As per tutorials, ExiSTS is having better performance as compared to IN in case of large data sets and vice-versa for smaller data sets. Could you please help me in understanding the logic and execution logic behind these two.

Consider the following two tables:

FOUR_WHEELER_TBL

Vehicle_IDManufacturerColor
1SkodaRed
2HondaSilver
3HyundaiWhite
4JagauarBlack
5MahindraRed



TWO_WHEELER_TBL

Vehicle2_IDManufacturerVehicle2_ColorVehicle4_ID
11HarleyBlack
12PiaggioBlue
13SuzukiSilver
14HondaBlack2
15MahindraRed5
16MahindraSilver2


Now, if we have to determine the 4 wheeler vehicles who has corresponding entry in two wheeler table and color is black. How to write the queries using exists and in clauses and what will be the flow of execution?
 
Ranch Hand
Posts: 207
2
Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is this your homework or what?
 
Vaibhav Gargs
Ranch Hand
Posts: 340
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Al Hobbs wrote:Is this your homework or what?



This is not my homework Al Hobbs. I am learning SQL basics. I got this example from one of youtube tutorials.
 
Sheriff
Posts: 23714
50
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're asking how the various database systems execute those statements?
 
Vaibhav Gargs
Ranch Hand
Posts: 340
2
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Paul Clapham wrote:You're asking how the various database systems execute those statements?



Yes Paul, I would like to understand how these queries are  internally executed by the DBMS
 
Paul Clapham
Sheriff
Posts: 23714
50
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If the DBMS is open-source, then you could download a copy of its source code and look there.

If it isn't open-source, then it's going to be a lot harder. You could contact the DBMS provider and ask them, but it may be that they feel their algorithms are proprietary and they won't tell you.
 
Master Rancher
Posts: 3630
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most (if not all?) relational DBs will provide a way of getting at the execution plan for a query, which will show you what order things are run and what sort of table scans/indexes are being used.

This is pretty much database dependent, though most will use similar tricks.

If you want to know what the query looks like, have you tried?
It looks to me like a simple join and filter rather than an IN or EXISTS in any case.
 
Bartender
Posts: 19731
92
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:
It looks to me like a simple join and filter rather than an IN or EXISTS in any case.



Me too. An inner join should do it.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!