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
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.
I want to insert and update records from dummy table to main table with same attributes on to conditions are same.
Example If Id and Date part are same in both table then records should be inserted
If id is not same in dummy table then record should be inserted
If id is same but date is not same in dummy then also records should be inserted
Shree – you need to read about “case” “when” option.
Table A Table B
————– —————-
id id
1 3
2 4
3 5
Output
id desc
———————- ————————
1 1 Present in A
2 2 present in B
3 3 present in both
4 4 prsent in B
5 5 present in B
please help me in this output.
Here is the script.
create table #t1(id int)create table #t2(id int)
insert into #t1 values(1),(2),(3)
insert into #t2 values(3),(4),(5)
select coalesce(t1.id,t2.id) as id,
concat(coalesce(t1.id,t2.id),' present in ',case when t1.id is null then 'table 2' when t2.id is null then 'table 1' else 'both' end)
from #t1 as t1 full join #t2 as t2 on t1.id=t2.id
…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
select t3.empname,t3.empplace
from (select t1.col_2,t2.col_2 from emp1 t1,emp2 t2
where t1.emp_id=t2.emp_id)t3
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…
Use LEFT JOIN
SELECT c.NAME, cr.ID
FROM comp as c LEFT OUTER JOIN car as cr on 1=1
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
You can using all the tables and use bcp
Read about bcp in SQL Server help file for more informations
Hi ,
I have 3 tables A,B and C where a , b and c have column name id , a .id is primary key , b.id and c.id is foreign key of a.id.
For example
A .Id has values 1,2,3,4
B.id has values like 1,1,1,1,2,3,3,3,4
C.id has values like 1,1,2,2,3,3,4
I want result like
a.id b.id c.id
1 1 1
1 1 1
1 1 null
1 1 null
2 2 2
2 null 2
3 3 3
3 3 3
3 3 null
4 4 4
Please Help me…
Hi,
I have two tables in the same DB say A and B but there are no common column.
but i suspect that some data are common.
A.id = 1,2,3…..
B.Value = 1,2,3
Is there a way to find the common data in these tables??
select a.id from a inner join b on a.id=b.value
Hi,
can any one help me…i want to eliminet the duplicate values while using INNER JOIN
This is the code.
SELECT DISTINCT H1.EMP_CODE,H1.NAME1A+’ ‘+H1.NAME2A+’ ‘+H1.NAME3A ‘NAME’
,H2.DESCA ‘JOB’
,H3.DESCA ‘COUNTRY’
,H4.DESCA ‘JOB’
FROM H_EMP H1 INNER JOIN H_D_JOB H2
ON H1.JOB_CODE=H2.CODE
INNER JOIN H_D_NATIONALITY H3
ON H1.NAT_CODE=H3.CODE
INNER JOIN H_D_DEPARTMENT H4
ON H1.DEP_CODE=H4.CODE ORDER BY EMP_CODE
hello ,
I am new to your blog, but i found it really helping. I also have a query. i have two tables rib_entry(date,amtcash,amtcheque,total) and bank_entry(date,amtcredited,total) now i need to print a report where amtcredited=amtcash or amtcredited=amtcheque. the two table have many to many relationship. the question is how can i join them to show matching records only on their first occurence on date basis, and they should not be matched twice.
i.e. if on 11/01/2010 amtcash=10000 and on 11/02/2010 amtcredited=10000 and on 11/02/2010 also amtcredited=10000 so the report should match 11/01/2010 amtcash to 11/02/2010 amtcredited only.
I have Two tables in same database.Both table has a common column.Each columns has value like this (1,2,3).So How can i get value from one table matching both tables with common field.Any suggest me with query.
Use inner join
select t1.* from table1 as t1 inner join table2 as t2 on t1.common_col=t2.common_col
Look like this inner join both table
SELECT E.ENAME, E.JOB,E.SAL, D.DNAME,D.LOC
FROM EMP E JOIN DEPT D
ON (E.DEPTNO=D.DEPTNO);
select e.ename,e.mgr,e.sal,e.hiredate,d.dept,d.loc,d,deptno from emp e,dept d
where e.deptno=d.deptno;
Hi, I have an issue where i cannot joing two tables but i need to get the data from both of them. I can achieve this by using cross join or by inline query. But which eats the performance. So wanted to know is there any alternatives for the same.
Table 1 contains required data say country names , region etc (no id’s) and the data is DB language specific. It may be in english in for one customer and German for another customer.
I need to write a logic where if countryname=’India’ then 1 else 0 kind of thing.
But as i said the values are in different lanaguages in differet DB i cannot compare this with harcoded text called ‘India’
For that i have created another table Table 2 which contains data like country name in all the languages and language code.
So i am writing query like below
select case when test.countryname=test.b then 1 else 0 end
from
(
select if Table1.countryname,(select table2.country name from table1 where language code=@parameter)b
) test
This is giving the required results but i have 100 such columns where i need to do the comparison. Is there any better way to achieve this
Thanks in advance
Sir , This Is My Query
With OrderedFoos As
(Select row_number() over (order by Bill Asc) RowNum, Bill,BillDate,Partyname,Vat,Tax From Purchase Where BillDate Between ’09-01-2011′ And ’10-30-2011′ And Tax=’0′),
OrderedBars as (Select row_number() over (order by Bill Asc) RowNum, Bill,BillDate,Partyname,Vat,Tax From Sales Where BillDate Between ’09-01-2011′ And ’10-30-2011′ And Tax=’0′)
Select P.Bill PBILL,P.BillDate PBillDate,P.Partyname PParty,P.Vat PVat,S.Bill SBILL,S.BillDate SBillDate,S.Partyname SParty,S.Vat SVat From
OrderedFoos P Inner join OrderedBars S on P.RowNum=S.RowNum
.. Working But Purchase table 10 Rows ,Sales Table 5 Row .. this query retriving first 5 records only but i have purchase table 10 rows and sales table 5 rows without duplication … how do … Please Help Me…….
Have you tried using a LEFT JOIN?
Sir,, I Have two tables date, join the record two table record in date wise but
first table record cout 10 and secord table record count 500 match two table date wise without duplication and null values
How do Please Help me Sir…