wave Hi any idea why `join` returns results but `left join`

Question

wave Hi any idea why `join` returns results but `left join` returns 0 results ```SELECT FROM docker container processes process JOIN docker containers container ON container id=process id``` vs ```SELECT FROM docker container processes process LEFT JOIN docker containers container ON container id=process id```

fritz · Answer

iirc the docker container process table has a required column for the where clause

fritz · Answer

as a result you need to ensure the tables are being joined in the appropriate order

fritz · Answer

You can force the SQLite engine to query in the expected order by utilizing a `CROSS JOIN` I have described this nuance in the following blog article <https blog kolide com running osquery as sudo root vs user 4fcfc698c45e>

fritz · Answer

So in your first case you want to be running ```SELECT FROM docker containers container CROSS JOIN docker container processes process ON container id=process id ```

fritz · Answer

The `LEFT JOIN` cannot work in the order you are trying query because you are trying to `SELECT FROM docker container processes` without a required where condition

ec4 · Answer

< fritz> Ohh interesting In the first case `join` alone appears to be working as I can see results In the 2nd case `left join` does not appear to be working Thanks I ll check out `cross` and `where` bow

fritz · Answer

< ec4> it is working by luck

fritz · Answer

basically what is happening is the following

fritz · Answer

sqlite sees your FROM and JOIN clauses

fritz · Answer

and it decides based on some internal rule logic which table to call from first

ec4 · Answer

gt < ec4> it is working by luck lol story of my life Thanks for explaining that

ec4 · Answer

It s strange I would expect it to compare it against the first table

fritz · Answer

if you happen to call a column from the table without the required where clause first it will order it that way

fritz · Answer

without using CROSS JOIN it just does what it thinks is best

fritz · Answer

which may or may not be what you expect want