Osquery uses basic SQL commands to leverage a relational data-model to describe a device.

osquery

&gt; Note: It is important to use CROSS JOIN as this tells the query optimizer not to reorder the evaluation of the tables. If we use a regular JOIN it is possible that reordering could result in the original error being encountered (because the chrome_extensions table generates with no uid in its context).
<@U0JFM04MS> Is this true? I've always been confused about why people use CROSS JOIN, since I'm used to traditional SQL where that includes NULLs, so I've always assumed LEFT JOIN. But I've never dug into the planner

It's not an issue with NULLs, it's an issue with the constraints being passed to the table generation function. If the generate function is called without the uids then it won't generate anything. So we need to tell the planner to generate the users first then the data that depends on user.

Right. I've always reached for LEFT JOIN for that. It's surprising to me that CROSS is better

<https://www.sqlite.org/optoverview.html#manual_control_of_query_plans_using_cross_join>

&gt; Table reordering is also disabled on an outer join, but that is because outer joins are not associative or commutative. Reordering tables in OUTER JOIN changes the result.

With `SELECT * FROM users LEFT JOIN chrome_extensions USING (uid)` you get a row for every user even when they don't have chrome extensions (with all the chrome extension columns NULL). With `SELECT * FROM users CROSS JOIN chrome_extensions USING (uid)` you get one row per extension (no row for any user that doesn't have extensions).

&gt; Programmers can force SQLite to use a particular loop nesting order for a join by using the CROSS JOIN operator instead of just JOIN, INNER JOIN, NATURAL JOIN, or a "," join. Though CROSS JOINs are commutative in theory, SQLite chooses to never reorder the tables in a CROSS JOIN. Hence, the left table of a CROSS JOIN will always be in an outer loop relative to the right table.
Basically the only difference between `JOIN`/`,` and `CROSS JOIN` is that the sqlite designers choose to interpret the cross join as a requirement to the optimizer not to reorder the tables.