Basic sql query problem [closed]

Question

Here is the scenario: lets say I have a database named sqlprac that contains a table named dept.

There are two ways to write a query:

use sqlprac
/*1*/
SELECT dept.deptno,
       dept.deptname,
       dept.deptsal
FROM   dept;

/*2*/
SELECT deptno,
       deptname,
       deptsal
FROM   dept;

Both queries should return the same result set. However I have seen that this isn't always the case, especially in sub query/inner query. What's the difference then?

Why should I choose one over another?

Also, a third query might be

/*3*/
SELECT *
FROM dept  

I know what * is doing, but in which case we should or not use it.

Are there any performance/optimization issues?

Answer 1

Both your queries are shorthand for this:

SELECT dept.deptno AS deptno, 
       dept.deptname AS deptname, 
       dept.deptsal AS deptname 
  FROM dept AS dept;

SQL is sometimes forgiving and will imply the AS clauses and correlation names, other times it requires them for no logical reason e.g.

SELECT deptno
  FROM dept JOIN emp USING (deptno);

will complain that deptno is ambiguous

SELECT deptno
  FROM (SELECT deptno FROM dept);

will complain that the derived table has no correlation name.

To answer the question: would would include, omit or revise the default correlation name to make the code to aid the human coder -- easier to read and understand means it will be easier to maintain -- but tastes vary (when the same table is involved in a query more than once then you need to used an alternative correlation name to disambiguate for the purposes of the parser, obviously).