Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
SELECT dept.deptno,
FROM   dept;

SELECT deptno,
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

FROM dept  

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

Are there any performance/optimization issues?

share|improve this question

closed as not a real question by Jack Douglas Jan 5 '12 at 18:55

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

This question should be moved to – Mike Nakis Jan 5 '12 at 12:42
There's three questions here: 1) what is the effect of not indicating scope, 2) why you should choose one form over another and 3) performance issues when using a particular form. I'd recommend that the question be split up among these three lines and asked separately. That way, more concise answers can be given and then addressed separately – casperOne Jan 5 '12 at 18:51
I'm closing this until we hear back from you about splitting the question up and rephrasing... – Jack Douglas Jan 5 '12 at 18:55
up vote 2 down vote accepted

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).

share|improve this answer
Perhaps make it clear that "no logical reason" flows from the USING because *of course" deptno is the same. The ambiguity would be bleeding obvious if this was LEFT JOIN .. ON ... However, isn't consistency better? So the choice of which deptno to choose doesn't depend on which JOIN construct is used... – gbn Jan 5 '12 at 12:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.