I really enjoy answering questions which I receive from either comments or Email. My passion is shared by SQL Server Expert Imran Mohammed. He frequently SQL community members by answering their questions frequently and promptly.
Sachin Asked:
Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,
1. Select column1,column2 From Table1
2. Select column1 From Table2
I want to find common records from these tables, but i don’t want to use Join clause bcoz for that i need to specify the column name for Join condition. Will you help me to get common records without using Join condition? I am using SQL Server 2005.
Imran Mohammed Replied:
If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.
SELECT column1, column2
FROM table1
WHERE column1 IN
(
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
)
To do this, make sure your column1 is unique and do not have duplicate records.
This is good answer. INTERSECT is new operator in SQL Server which gives you similar answer without using JOIN. I have previously written article where I have compared INTERSECT with INNER JOIN I suggest that all user read that article for further clarity.
SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN
I would appreciate my readers input about this article and if you know any alternative method, it will be interesting to see them.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Hello,
I think this is much simpler,
select A.column1, A.column2
from table1 A, table2 B
where A.column1 = B.column1
The advantage of this is, you will get the complete result set, Intersect will not return duplicates.
Thanks,
Imran.
…but you still had to specify the column name in the intersect. So I’m not seeing the benefit or how this answers the challenge. Furthermore, I have to assume that this method is wildly inefficient compared to the inner join method because it probably can’t use the table indexes the same way due to the way the IN statement fetches and compares the data. I’d recommend you attach the estimated plans for the JOIN method versus the INTERSECT method.
A “bad” answer to “how else can it be done?” would be to use SQL 87 syntax (which you also use the column name, but not the join syntax):
SELECT tbl1.* FROM tbl1, tbl2 WHERE tbl1.Column1 = tbl2.Column1
However, I believe the most natural way is, well, the natural join. This is as efficient as an inner join _and_ it satisfies the requirement of not specifying columns:
SELECT tbl1.* FROM tbl1 NATURAL JOIN tbl2
But before you go and try this join in MS SQL Server, MS SQL Server doesn’t support natural join syntax like that. Instead, it calls a natural join/equi-join a join where you use the equal operator on the ID columns within the INNER JOIN constraints.
In closing, I think it’s important that you make an important distinction to your readers. While I understand showing that you _can_ do it, I think the important question to answer is _should_ you do it. In terms of efficiency and readability, I would still put my money on INNER JOIN. And I think you owe it to your readers to do some quick benchmarking to let them know if they should ever consider this approach over join syntax in real world situations.
hi can i get some sample questions using joins.
how to get common columns from all the tables of database???
Please do tell me…
Regards
Farhan
Hi,
I have Emp_Id,EMP_Name in emp1 table and Emp_Id,Emp_Place in emp2 table, i want Emp_Name and Emp_Place from both the tables without using join.
One more thing Emp_Id is a common column in both the tables.
Can anybody help me?
Regards,
Sandeep
How can I join two table (with a query) that, one table has no data and the other has some data!!???
this is because some times my data base may have a table with or with out data.
thanks
I can’t use:
SELECT comp.NAME, car.ID
FROM comp, car
it returns an empty table…
hello…
i got a problem..
i want to join 2 table..
1st table get data from source file and 2nd table get data
from other source file…
but many column from 2nd table must get data from 1st table..
how can i do it?
huhuhuh
please help me
tq
Hi Farhan,
Here is the query to get columnname and its count in all the tables
SELECT name, COUNT(name) FROM sys.columns
GROUP BY name
This will give you the tablename and columnname
SELECT t.name, c.name
FROM sys.columns c INNER JOIN sys.tables t ON c.object_Id = t.object_id
GROUP BY c.name, t.name
Thanks
Nizam
Hi Schmack,
Why don’t you use FULL OUTER JOIN.
!@noor
you can use ssis
Hello sir i m using sql 2000 in asp.net, i want to download a file from two tables indivuadually but i can do but i want from 2 tables Q1 and Q2, and download in csv format? wat can i do sir plz help me send me a mail regarding this thanks