SQLite - SELECT query returning more than expected

Question

Hi,

In the following my SQLite database has 10 values in each table. However, when performing my query there are around 100 results returned.

 SELECT videoformat, audioformat FROM videocodec, audiocodec

Any ideas with why it is doing this?

Thanks

Answer 1

You're getting back all combinations of rows from both tables, also known as the Cartesian product. 10 x 10 = 100.

Answer 2

Your SQL says: for each row in videocodec, select all rows in audiocodec. That will give 10x10 or 100 rows. It's equivalent to:

select  *
from    videocodec
cross join
        audiocodec

You probably mean to retrieve both audio and video codecs. You can do that with a union:

select  codecname
from    videocodec
union
select  codecname
from    audiocodec