There are two tables in SQL Server
table1 (Id INT)
table2 (Id INT)
in table1 there are ten records and in table2 contains 0 record
when i select from both tables
Select * from table1, table2
in output window there is no result display..
but when i inserted a new record in table2 and execute again same above select statement then it will display table1 record along with table2 records.
I am getting confused why first select statement does not show any record?
10*0=0. That siple. As table 2 contains 0 records the resulting record set is empty = 0 lines.
The query produces something called Cartesian Product, which is basically every record from one table joined to every other from another. Therefore total number of records returned is
(records in Table1) x (records in Table2)
But since Table2 has 0 records, the Cross product has 0 records - because it could not find any record in other table to join to with each record in first table.
When you select from multiple tables separated with comma then you are actually asking for a "Cartesian product" (see https://secure.wikimedia.org/wikipedia/en/wiki/Cartesian_product) of the two tables. If table table1 has X records and table2 has Y records, then the result will contain X*Y records. That is zero records if one of the tables contains zero records.
FROM table1, table2
is a Cartesian product of the two tables, the same as
FROM table1 CROSS JOIN table2
Both will show every combination of a row from table1 with a row from table2.
So, 10 x 0 = 0 combinations.
The operation you are doing is called a cartesian join (multiset multiplication): it will output all possible combinations of records in the tables.
Since there are no records in the second table, there are no combinations possible.
If you want to concatenate the multisets (do a multiset addition), use UNION ALL instead:
SELECT id
FROM table1
UNION ALL
SELECT id
FROM table2
