Laxmi
Laxmi

Reputation: 3800

SQL query for returning rows from two tables

Can anyone explain me how this query is working. Thanks in advance.

image1

Upvotes: 1

Views: 129

Answers (4)

Chandana Kumara
Chandana Kumara

Reputation: 2645

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.

Upvotes: 1

Chanukya
Chanukya

Reputation: 5883

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...

Upvotes: 1

arkki
arkki

Reputation: 102

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;

Upvotes: 2

Harshil Doshi
Harshil Doshi

Reputation: 3592

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!!

Upvotes: 2

Related Questions