https://github.com/osquery/osquery logo
Join Slack
Powered by
:wave: Hi, any idea why `join` returns results bu...
# general
e
👋 Hi, any idea why 
join
returns results but 
left join
returns 0 results?
Copy code
SELECT *
FROM docker_container_processes process
JOIN docker_containers container ON container.id=process.id
vs.
Copy code
SELECT *
FROM docker_container_processes process
LEFT JOIN docker_containers container ON container.id=process.id
f
iirc the docker_container_process table has a required column for the where clause
as a result you need to ensure the tables are being joined in the appropriate order
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
So in your first case you want to be running:
Copy code
SELECT *
FROM docker_containers container
CROSS JOIN docker_container_processes process ON container.id=process.id;
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.
e
@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
🙇
f
@ec4 it is working by luck
basically what is happening is the following
sqlite sees your FROM and JOIN clauses
and it decides based on some internal rule logic which table to call from first
e
@ec4 it is working by luck
lol story of my life. Thanks for explaining that
It’s strange. I would expect it to compare it against the first table
f
if you happen to call a column from the table without the required where clause first, it will order it that way
without using CROSS JOIN, it just does what it thinks is best
which may or may not be what you expect/want
til 1
8 Views
Open in Slack
PreviousNext
Powered by