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 (https://blog.sqlauthority.com)
97 Comments. Leave new
I have 3 tables, Student, Subject, MappingStutoSub. In student table, StuId id primary key, Subject table had Subid as Primary key, In Mapping table the Stuid & Subid will be there based on the choice. Now, I want to show all the Subjects and the subjects which are mapped to one student. How to do this?
Couldn’t the same be achieved by:
Select column1 from table1
Where column1 in(
Select distinct column1 from table2)
How to find unique records from columns of two tables.
Does not the above query work? If not, use DISTINCT or UNION
select col from table1union
select col from table2
SELECT * FROM CustomCADMaster tt
Left Join
(SELECT OrderNo, MAX(ImgName) AS ImgName
FROM CADOrderDet GROUP BY OrderNo)
groupedtt ON tt.OrderID = groupedtt.OrderNo
where OrderStatus= ‘Open Order’
i want additional Field in subQuery
Additional Fields CADNotes, FactoryNotes,ImgFileType
i have two tables with no common fields but i want to run a query to get a result based on text keywords which both the tables contain Eg: table A contains field ‘Operating system’ with the content `Windows’,and table B contains a field ‘Patches’ containing the word ‘Windows’ as well
T1:- Name T2- Name
———— ————-
Mangoes Mangoes
Grapes Grapes
Banana
How to find the uncommon Name from 2 tables without using Sub query, Not, Except, Intersect, .
Would something like below work? Seems to be kind of a “hack” but should do the job.
SELECT Name
FROM
(
SELECT DISTINCT Name FROM T1
UNION ALL
SELECT DISTINCT Name FROM T2
)
GROUP BY Name
HAVING COUNT(Name) = 1