Get Common Records From Two Tables Without Using Join

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
)