Win a copy of Spring in Action (5th edition) this week in the Spring forum!
  • 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:
  • Campbell Ritchie
  • Bear Bibeault
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • paul wheaton
Saloon Keepers:
  • Ganesh Patekar
  • Frits Walraven
  • Tim Moores
  • Ron McLeod
  • Carey Brown
Bartenders:
  • Stephan van Hulst
  • salvin francis
  • Tim Holloway

EXISTS and IN execution logic  RSS feed

 
Ranch Hand
Posts: 370
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: 370
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: 23867
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: 370
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: 23867
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.
 
Rancher
Posts: 3748
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: 19976
95
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!