CODEWITHSUNDEEP
mysqlsqlt-sqlalias
Dmitrii
Dmitrii

Reputation: 173

Understanding an SQL Alias

I'm new to SQL. I've read the book called 'Sams Teach Yourself Oracle PL/SQL in 10 minutes'. I found it very interesting and easy to understand. There was some information on aliases but when I started doing exercises I came across an alias I don't know the purpose of.

Here is the cite http://www.sql-ex.ru/ and here is the database schema http://www.sql-ex.ru/help/select13.php#db_1, just in case. I'm working with the computer firm database i.e database number 1. The task is:

To find the makers producing PCs but not laptops.

Here is one of the solutions:

SELECT DISTINCT maker
           FROM Product AS pcproduct
          WHERE type = 'PC' 
            AND NOT EXISTS (SELECT maker
                              FROM Product
                             WHERE type = 'laptop' 
                               AND maker = pcproduct.maker
                           );

The question is: Why do we need to alias product as pc_product and make the comparison 'maker = pc_product.maker' in the subquery?

Upvotes: 1

Views: 169

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You are accessing the table twice, once in the main query, one in the sub query.

In the main query you say: Look at each record. Dismiss it, if the type doesn't equal 'PC'. Dismiss it, if you find a record in the table for the same maker with type 'laptop'.

In order to ask for the same maker, you must compare the maker of the main query's record with the records of the subquery. Both stem from the same table, so where product.maker = product.maker would be ambiguous. (Or rather the DBMS would assume you are talking about the subquery record, because the expression is inside the subquery. where product.maker = product.maker would hence be true, and you'd end up checking only whether there is at least one laptop in the table, regardless of the maker.)

So when dealing with the same table twice in a query, give at least on of them an alias in order to tell one record from the other.

Anyway for the given query I'd also qualify the other column in the expression for readability:

AND product.maker = pcproduct.maker

or even

 FROM Product laptopproduct
WHERE type = 'laptop' 
  AND laptopproduct.maker = pcproduct.maker

On a sidenote: The query looks for makers that produce PCs, but no laptops. I'd prefer asking for this with aggregation:

select maker
from product
group by maker
having sum(type = 'PC') > 0
   and sum(type = 'laptop') = 0;

Upvotes: 2

Richard Hansell
Richard Hansell

Reputation: 5403

This doesn't detract from the other (correct) answers, but an easier to follow example might be:

SELECT DISTINCT pcproduct.maker
           FROM Product AS pcproduct
          WHERE pcproduct.type = 'PC' 
            AND NOT EXISTS (SELECT internalproduct.maker
                              FROM Product AS internalproduct
                             WHERE internalproduct.type = 'laptop' 
                               AND internalproduct.maker = pcproduct.maker
                           );

Upvotes: 0

LukStorms
LukStorms

Reputation: 29647

That query can be understood as :

Gimme the maker's that have products of the 'PC' type, but where a product of the 'laptop' type doesn't exist for that maker.

Including the tablename or alias name is sometimes needed when the same column names are used in more than 1 table.
So that the optimizer will know from which table the column is used.

It's not some smart AI that could guess that a criteria as
WHERE x = x
would actually mean
WHERE table1.x = table2.x

But more often, shorter alias names are used.
To increase readability and make the SQL more concise.

For example. The following two queries are equivalent.

Without aliases:

SELECT myawesometableone.id, mysecondevenmoreawesometable.id, 
       mysecondevenmoreawesometable.col1
FROM myawesometableone
JOIN mysecondevenmoreawesometable on mysecondevenmoreawesometable.one_id = myawesometableone.id

With aliases:

SELECT t1.id, t2.id, t2.col1
FROM myawesometableone AS t1
JOIN mysecondevenmoreawesometable AS t2 on t2.one_id = t1.id

Which SQL do you think looks better?

As for why that maker = pc_product.maker is used inside the EXISTS?
That's how the syntax for EXISTS works.
You establish a link between the query in the EXISTS and the outer query.
And in that case, that link is the "maker" column.

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

Because in the inner query there are columns, which are named exactly the same as those in outer query (due to the fact that you use the same table there).

Since outer query columns are available in the inner query, there must be a distinction, which column you want, without alias, you'd write in the inner query maker = maker, which would be always true.

Upvotes: 3

Related Questions