CODEWITHSUNDEEP
androidsqlselectsqlite
Miguel Ribeiro
Miguel Ribeiro

Reputation: 8184

Select from multiple tables shows no results if one is empty sqlite

I'm using this in android but using SQLite Studio 2.1.4 for testing.

I have two tables: A and B. A has 2 records and B has no records.

If I query 

select * from A

I get the results in that table, which is fine.

If I query

select * from B

I get no results, which is also fine.

However, if I query:

select * from A, B

I also get no results, which is wrong... I should get the results from A, right?

Or is SQLite somewhat different from the other DBMS out there?

Upvotes: 0

Views: 187

Answers (1)

NigelK
NigelK

Reputation: 8490

SELECT * from A, B is a cartesian join of both tables. In other words every row in table A is joined to every row in table B. So if A has 5 rows and B has 10 rows, you end up with 50 rows in your results set. In this case, table B has zero rows hence it doesn't matter how many rows A contains, you get no rows back.

You will find the same behaviour in all relational databases.

Upvotes: 4

Related Questions