CODEWITHSUNDEEP
mysqlsqldatabase
Aswin Prasad
Aswin Prasad

Reputation: 417

Explanation of the SQL query below

Below is the sql query to select the the name of all the instructors, who gets salary at least more than one instructor.(That is leave the least paid one instructor). This query is from Database System Concepts by Silberchatz, Korth. But i can't visualise how the query is used, aside one thing, that is as is necessary to do it in this way.

You can visualise the table i guess.

select distinct T.name
from instructor as T,instructor as S
where T.salary > S.salary;

Upvotes: 1

Views: 329

Answers (2)

MatBailie
MatBailie

Reputation: 86706

Another way of writing that query is this...

SELECT DISTINCT
    T.name
FROM
    instructor  AS T
CROSS JOIN
    instructor  AS S
WHERE
    T.salary > S.salary

In general CROSS JOINs are a bad idea. And here they're a really bad idea. If you have 100 instructors, you evaluate 4950 different combinations to get a unique list of 99 instructors.
- Instructor001 has 99 instructors who are paid less
- Instructor002 has 98 instructors who are paid less
- ...
- Instructor100 has 00 instructors who are paid less
- Totalling 4950 combinations

If you have 1000 instructors you evaluate 499500 combinations to get a unique list of 999 instructors.

A better idea would be...

SELECT
    i.Name
FROM
    instructor    i
WHERE
    i.salary > (SELECT MIN(salary) FROM instructor)

Or...

SELECT
    i.Name
FROM
    instructor    i
WHERE
    i.Name <> (SELECT Name FROM instructor ORDER BY Salary ASC LIMIT 1)

Or...

SELECT
    i.*
FROM
    instructor    i

EXCEPT

SELECT
    i.*
FROM
    instructor    i
ORDER BY
    Salary ASC
LIMIT 1

So, you're right, it's not necessary (or even a good idea) to do it that way.

Upvotes: 2

Jakub Matczak
Jakub Matczak

Reputation: 15656

Let's refactor this query a little bit.

SELECT DISTINCT T.name
FROM instructor as T
JOIN instructor as S ON T.salary > S.salary;

It does exactly the same thing, but it expressed with different syntax ( and does it more efficiently). This way it's more readable.

Now, the T table is base table that you query. Next, you join the same table under S alias to match other instructors that are less paid (T.salary > S.salary), which means that you exclude from T results rows for which such S instructor doesn't exist.

At the end you use DISTINCT keyword in SELECT clause, in order not to get duplicated T instructors for which there are more than one less paid S instructor.

Upvotes: 0

Related Questions