How Does SQL Statement Work

Question

0.00/5 (No votes)

See more:

I have a question regarding the execution of this query:

SQL

SELECT
    student.id,
    student.name,
    student.class,
    teachers.name
FROM student 
INNER JOIN admin
    ON admin.id = student.id
WHERE
    student.id <> 0

I think it first executes the below statement:

SQL

SELECT
    student.id,
    student.name,
    student.class,
    teachers.name
FROM student
WHERE
    student.id <> 0

I think we will get a table, and then the table which we got from above query will compare with the inner join statement:

SQL

INNER JOIN admin
    ON admin.id = student.id

Am I correct in my above thinking?

Posted 21-Dec-10 7:05am

swathi.N

Updated 21-Dec-10 7:25am

AspDotNetDev

v2

Add a Solution

Comments

AspDotNetDev 21-Dec-10 13:27pm

FYI, I reformatted your question. It was hard to read otherwise. Also, where did the "teachers" table come from? That query shouldn't even run.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2010-12-21T07:19:00

Solution 1

No, you are not correct.

There are different algorithms, depending on the DB server, indexes and data order (clustered PK), whether calculated values are joined or not etc.

As, suggested here[^], have a look at a query plan, which most SQL systems can create for a query, it should give you an idea what it does.

For more read this[^].

Posted 21-Dec-10 7:19am

Sandeep Mewara

Comments

Manfred Rudolf Bihy 21-Dec-10 13:28pm

Nice links, thanks! 5+

Martin Arapovic · Answer 2 · 2010-12-21T07:37:00

This is the execution order of sql server select command:
1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

If you look at the order again you can conclude that FROM is logical to be on first place, because executing it first sql server can dramatically narrow down possible record set sizes. Then it goes WHERE and so on...

Further more there is a several logical joins statements available in sql server, but sql implements these logical operators with "three" different physical operators:
1. Nested Loops Join
2. Merge Join
3. Hash Join
Which one of them is used is a "mistery" to me also ;), because sql have internal logic when and what join type to use. We could write about This is very wide topic, so if you want more information about this google or find some good e-book about it. ;)
My choice is "SQL Server MVP Deep Dives", it is a contributions from many SQL Server MVPs, and you have explained everything.

I hope that I helped at least a little. ;)