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
Hi,
Suppose I have two tables with a few columns with a common name,now I need to Full Outer join ON Account_ID them such that I get everything in one table. But I dont want it to show the common columns twice ie Account_ID column should be one with rows of both tables (one after the other if the Account_ID r not matching)
hii, suppose we have two tables A and B
A has output like this
A_id
1
2
3
4
5
B has output like this
B_name
neha
priya
rashmi
How can we get output like
A_id B_name
1 neha
2 priya
3 rashmi
4
5
We can get the output using third table but how ? Please help..
Which version of SQL Server are you using? An easy way is to generate row number for each table and join with it to get the result you want
SELECT “invoice”.”ITEMNO”, “invoice”.”Outgoing”, “storeage”.”incoming” FROM “MVXJDTAP”.”MVXJDTA”.”invoice” INNER JOIN “MVXJDTAP”.”MVXJDTA”.”storeage ” ON “invoice”.”ITEMNO” = “storeage”.”itemno”
ORDER BY”storeage”.”itemno”
false RESULT :
invoice storeage FALSE result
itemno Outgoing itemno incoming itemno outgoing incoming
ZOMV3-94 4 ZOMV3-94 3 ZOMV3-94 4 3
ZOMV3-94 7 ZOMV3-94 1 ZOMV3-94 4 1
ZOMV3-94 60 ZOMV3-94 4 60
ZOMV3-94 5 ZOMV3-94 4 5
ZOMV3-94 7 3
ZOMV3-94 7 1
ZOMV3-94 7 60
ZOMV3-94 7 5
HOW CAN GET THIS RESULT
TRUE RESULT :
ZOMV3-94 4 NULL OR 0
ZOMV3-94 7 NULL OR 0
ZOMV3-94 NULL OR 0 3
ZOMV3-94 NULL OR 0 1
ZOMV3-94 NULL OR 0 60
ZOMV3-94 NULL OR 0 5
I WANT CODE TO RUN TRUE RESULT
Display one record from table without using where condition?
select top 1* from tab_name
How to retrieve consecutive records from the table starting from particular location?
e.g., There are 1000 records in the table. I want to retrieve records from location 750 to 775. Is there any way to do this?
select * from (select Row_Number() over (order by ColumnNames asc) as RowIndex, * from TableName) as Sub Where Sub.RowIndex >= 750 and Sub.RowIndex <= 775
Thanks for your help @Naresh.
how i can retrive two different columns like(ename,deptname) from two different table like(emp,,dept) without using the join conditions
……………………help me
Without JOIN it will become CROSSN JOIN. Can you post some sample data with expected result?
Hello everyone. Need some help here. I have two tables with the same amount of columns and also the same column names. The first table has 29 rows and the second table has 31 rows. I want to filter the new rows (2) out based on a query. Intersect, NOT EXISTS do not work.
My server version is 5.1.41 MySQL client version is 5.1.41 and my phpMyadmin version is 3.2.4. Please can anybody help me with a query?
I suspect if INTERSECT work in MySQL. Have you tried LEFT JOIN with NULL condition or NOT EXISTS?
hello,
i have created two tables named course name, student details. i have five records for each course. now i need out put as top three records of each course to be shown. send the query
greetings right from united states man, you actually made my day with this habbo coins hack
please tell me….. What is minimum no of joins using to combine 5tables? please write a query for it.if any one knows…
suppose i have three tables like tblStud,tblBatch,tblAttendance i have to show the name of student and status of the student. tblStud(studID,BatchID,StudName), tblBatch(batchID,batchName) tblAttedance(attID,studID,Status,Doa) when i select batch it will display Record here currentyly tblAttendane have no any data.
I have two tables tbl1(cid,cname,caddress) and tbl2(vid,vname,vaddress).
I need a query to display data from both tables as
id,name,address which displays all data from tbl1 and tbl2.vid>10. can you help anybody?
If question is not clear, plz mention.
Sample create table, dummy data and expected output would help.
you can post it on also.
You can join these two tables using the key columns and apply the filter in WHERE clause
i have two table table1 and table2
table1
1,2,3,4,5,6,7
table2
3,5,6
get output like bellow
1,2,4,7
select col from table1 where col1 not in (select col2 from table2)
Hello,
I need common records in 2 tables. There is no common identifier in those 2 tables. They have IDs present in both the tables BUT there relatioship is in the 3rd mapping table. What is the best way to get the output.
I want to join two tables namely comments and users in which both table have user_id for which in comments in foreign key in other it is primary.But the number of rows are not same
Hi I have table1 insert with fields filed1, field2, field3 which is looking for id from table2.
table1 consist of id’s and need to get value of this id from table2.
how can i get the value of this 3 fields from table2 to show on datagrid in vb.net.
Thanks guys
Anyone can response?
We have two table table1 and table2 in both table column is different but value are same in both table means one person has his one id with two columns colm1,colm2 and in another table2 same person has different column name colm3 ,colm4 with same value so we require one query to avoid that person who lying in both table
SELECT column1, column2
FROM table1
WHERE column1 IN
(
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
)
I WAS TRYING THIS CODE , BUT I AM GETTING MISSING EXPRESSION ERROR.
can you post sample?
Can you help me on this sir?
I want to use INNER join but I don’t get the logic of it. Here’s my problem I want to get access on my second table using my first table like if I Log in Specific user the records or data of that user must view
example
USER 1 log in his/her account then
he/she wants to view his grade
Like that sir?? please reply thank you
you can use view.