Retrive data from more than one table without using joins

Well...yes. But it's a stupid idea!

If you have a link between the two tables, then you presumably want to return data on a row that is only relevant to that row - and the best way to do that is with a JOIN.
If you don't return only the relevant rows:

SELECT * FROM MyTable, MyTable2

Then it can only retirn each row of MyTable2 for each row of MyTable: so if you have 6 Rows in MyTable and 2 in MyTable2, the it will return 12 rows.

If you use a join:

SELECT * FROM MyTable m1
JOIN MyTable2 m2 ON m1.ID=m2.Id

Then it returns only the rows you are interested in.
You could do it without a JOIN if you were really desperate:

SELECT * FROM MyTable, MyTable2 WHERE MyTable.ID=MyTable2.Id

And it will return the same rows.

In performance terms, there is no difference, but the JOIN form is a lot easier to maintain, particularly if the query becomes more complex.
Good practice recommends the JOIN over the WHERE form.

visit here this will help you

http://blog.sqlauthority.com/2008/10/17/sql-server-get-common-records-from-two-tables-without-using-join/[^]

SELECT
    e.EmpId
    , e.EmpName
    , e.CityId
    , (SELECT TOP 1 c.CityName FROM City c WHERE c.CityId = e.CityID) AS CityName
FROM
    employee e

select e.EmpId,e.EmpName,f.CityName from Employee e,City f where e.CityId=f.CityId

SELECT EmpId,EmpName,CityName FROM employee  CROSS JOIN City WHERE City.CityId =  employee.CityId