Columns in SQL SELECT without aliases A. B.

[HeatherNewton]

Hi when i use join for two table i know i van use select a. b. To specify where to get the table. For example

Select a.apple a.lemon b.size b.colour
weight origin
from fruit a left join info b on a.cost=b.cost

But what if I have some variable where no a. Or b like weight and origin in the above Next to it, how do i know where these variAble is from?

[Kurt_Bremser]

Maxim 3: Know Your Data. Inspect the input datasets to see where the variables come from.

[HeatherNewton]

I know it is from a, but what i dont underratand is why for some u put a. and for some u dont have to.. and it knows where to get it..

[fja]

As a rule of thumb if the column is unambigous you do not have to qualify the table using a qualified name. If there is some ambiguity (eg. both tables contain a column called bla) you have to use the a.bla ... to specify the column to select.

Maybe you have a little play in SAS and try it yourself: 

data work.TestData1;
	input ID A$ B$ C$ bla$17.;
	datalines;
	1 a1 b1 c1 bla1_fromTableOne
	2 a2 b2 c2 bla2_fromTableOne
	;
run;
data work.TestData2;
	input ID D$ E$ F$ bla$17.;
	datalines;
	1 d1 e1 f1 bla1_fromTableTwo
	2 d2 e2 f2 bla2_fromTableTwo
	;
run;

proc sql;
	select A, B, C, D, Two.bla from TestData1 as One, TestData2 as Two where One.ID = Two.ID;
	quit;
run;

--fja

[fja]

Hello!

Though I completely agree with Kurt's answer above, I find this topic intresting enough to add some thoughts.

This is tricky! What you do by the join is that you assemble a new table ... the naming of its columns are derived from the qualified and unqualified names ... or AS expressions if given. 

From that, any unqualified name in the select part could origine from any source table and is not determined by the select statement - if given in the form quoted.

For short: As @Kurt_Bremser has aready put it, you need to have a look at your data. Now it is that tables happen to change and columns get added form time to time. For that reason my suggestion would be to use qualified names whenever possible. ... but this is of course beyond the question posed.

--fja

[s_lassen]

how do i know where these variAble is from?

You don't. Not unless you look at the input data. Your example code shows a lot about how NOT to do things in SQL:

• Table aliases are just "a" and "b", not very telling. It gets really horrible when you have large joins. I once had to debug a join where the aliases went all the way from "a" to "k", and when some of the tables entered the joins more than once, the first instance was called "i" and the second "ii". Not very readable.
• It is not specified where all columns come from
• program is badly formatted

So I would probably rewrite it as something like this (just guessing as to where the unspecified columns come from):

Select 
  fruit.apple,
  fruit.lemon
  info.size,
  info.colour,
  info.weight,
  info.origin
from fruit left join info 
  on fruit.cost=info.cost

[PGStats]

You can use the FEEDBACK option:

proc SQL feedback;

Select a.apple, a.lemon, b.size, b.colour,
weight, origin
from fruit a left join info b on a.cost=b.cost;

quit;

it will expand the query in full details.