CODEWITHSUNDEEP
sql-server
Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

SQL Server query output?

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?

Upvotes: 1

Views: 172

Answers (5)

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 0

Ole Tolshave
Ole Tolshave

Reputation: 291

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.

Upvotes: 1

d-live
d-live

Reputation: 8036

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.

Upvotes: 1

TomTom
TomTom

Reputation: 62093

10*0=0. That siple. As table 2 contains 0 records the resulting record set is empty = 0 lines.

Upvotes: 1

Related Questions