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.
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.
If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.
SELECT column1, column2
WHERE column1 IN
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.
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)