concatenate two selects results

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)

concatenate two selects results

Author

Topic

Mazenx
Starting Member

5 Posts

Posted - 2011-02-05 : 10:09:46

hello..I have a table named "Students" ( ID , STUDENT_NAME )
and table named "COURSES" ( ID , COURSE_NAME , STUDENT_ID) , one to one relation

I have two queries
select * from students.
select * from courses

how can i make the two results show next to eachother
like this
ID STUDENT_NAME , ID COURSE_NAME , STUDENT_ID

But i dont want to create two joins , I want two different queries but their results appear with eachother ( next to eachother ).

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-05 : 11:47:45

Technically, you can't. Not sure why you would want to either.

Syntactically, you could do this:

select	s.ID, s.STUDENT_NAME, (Select ID from COURSES), (SELECT COURSE_NAME from COURSES), (SELECT STUDENT_ID from COURSES)
from	Students s

but that will perform a LEFT OUTER JOIN internally.

Or you could do this:

SELECT	*
FROM	Students, COURSES

but this one will perform an INNER JOIN.

What is the purpose for retrieving rows from both tables without using a join?

Mazenx
Starting Member

5 Posts

Posted - 2011-02-06 : 04:51:40

Okay , here is the thing I've just discovered!
I have a table with almost 20 columns , and another table with 5 columns ( they have foreign key ) and another with 5 columns too
they all have more than 200,000 records.

so I wrote a query to make a join between them to retrieve only one single record
It's taking 60/1000 of the second , that means almost .1 from the second !

then I wrote the three queries , they make a select from the three tables based on given id ( the three select results i want to concatenate )
and It's taking less than 15/1000 of the second !! so the second solution is 4 times faster than regular join !

so i think if the tables has like 2 million records...it might be more than 15 times faster , like a query might take a 15 seconds with regular join and 1 second with my idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources