Selecting columns from different tables w/o joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting columns from different tables w/o joins

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2014-06-12 : 10:27:40

Let's say I have two tables, A and B:


TblA
-------
Col1   Col2
1      a
2      b
3      c
3      d
4      e

TblB
-------
Col1   Col2
1      aa
2      bb
4      cc

I want to view DISTINCT values of Col1 of Table A and ALL values of Col1 of Table B in one query, side-by-side:


A.Col1    B.Col1
1         1
2         2
3         4
4

I have done this query a long time ago, but I can't recall how. I think I employed a subquery of the SELECT clause but maybe I'm wrong. Here's what I have so far (which doesn't work):


SELECT 
    ACol1 = (SELECT DISTINCT Col1 FROM TblA),
    BCol1 = (SELECT Col1 FROM TblB)
FROM TblA

Thank you very much.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-12 : 10:39:05

is there no relationship between the two tables (common key). If not, can the results be in any order at all? Why don't you want to use a join?

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2014-06-12 : 10:48:37

Correct, technically, there is no common key between the two tables. However, Col1 of both Table A and B are similar and I want to compare them in one table, to see if there is a direct relationship I can use. The results don't have to be in any order but it would be helpful.

I suppose I could use a join, but I recall there is a way to select columns from separate tables using subqueries. I thought this can be readily done without using joins. Maybe that is not the case and I'm just remembering something incorrectly?

Any help would be appreciated.

Thank you.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2014-06-12 : 10:59:11

I managed to get what I wanted with an OUTER JOIN. I guess I was wrong about it being possible selecting columns from different tables without employing joins.


SELECT A.Col1 A_Col1, B.Col1 B_Col1
FROM TblB B
LEFT OUTER JOIN (
	SELECT DISTINCT Col1
	FROM TblA)A ON A.Col1 = B.Col1

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-12 : 14:04:04

You could use a cross join. That would show you all combinations from tables A and B

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2014-06-12 : 15:23:16

That's a good idea. I will try that. Thank you for your help.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-12 : 16:12:59

A FULL OUTER JOIN will show you a row if it exists in either table. It's essentially a RIGHT and LEFT join at the same time.



SELECT A.Col1 A_Col1, B.Col1 B_Col1
FROM TblB B
FULL OUTER JOIN (
	SELECT DISTINCT Col1
	FROM TblA) AS A ON A.Col1 = B.Col1
ORDER BY A_Col1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources