Can anyone explain me how this query is working. Thanks in advance.
1
-
2Please tag the RDBMS you are using.Shushil Bohara– Shushil Bohara2016-10-18 05:05:01 +00:00Commented Oct 18, 2016 at 5:05
Add a comment
|
4 Answers
That query is used for returning a cartesian product (https://en.wikipedia.org/wiki/Cartesian_product) of both tables.
In this case 100 x 0 equals to 0 rows in total. Similarly 100 x 10 would equal to 1K rows.
The query itself can also be written with:
SELECT Tbl1.* FROM Tbl1 CROSS JOIN Tbl2;
OR
SELECT Tbl1.* FROM Tbl1 INNER JOIN Tbl2 ON 1 = 1;
Comments
Select t1.* from t1,t2;
In above statement, t1,t2 perform cartesian product of t1 and t2. But as we know that t2 is empty, the result of this cartesian product is an Empty set (or empty table in terms of DBMS).
Hence, it will return 0 rows!
Hope it will help!!
Comments
SELECT Tbl1.* FROM Tbl1 CROSS JOIN Tbl2;
SELECT Tbl1.* FROM Tbl1 INNER JOIN Tbl2 ON 1 = 1;
In above statement tables t1,t2 perform cartesian product of t1 and t2(t1*t2)
table t1 having 100 records and table t2 having 0 records
so cartesian product of t1 and t2(t1*t2)
=100*0
=0
so it will not return any records...
Comments
There should be a relationship between these two tables. Then you can apply INNER JOIN to retrieve data.
OR you can use below query.
Select t1.* from t1,t2 WHERE 1 = 1;
WHERE 1 = 1 specially useful when develop dynamic queries. Because it can add one or more condition without checking already exists conditions.
