ccc
01/31/2019, 6:33 PMSELECT * FROM users JOIN chrome_extensions USING (uid);
works and returns the list of extensions, but SELECT * FROM users JOIN chrome_extensions USING (uid) WHERE identifier='gkojfkhlekighikafcpjkiklfbnlmeio';
returns empty even though its therezwass
01/31/2019, 6:35 PMJOIN
with CROSS JOIN
ccc
01/31/2019, 6:35 PMzwass
01/31/2019, 6:38 PMCROSS JOIN
for those queries where you need to join against the users table. The sqlite optimizer will sometimes change the order of the join otherwise and cause false negatives.clong
01/31/2019, 8:44 PMzwass
01/31/2019, 8:45 PMcross join
.clong
01/31/2019, 8:46 PMdefensivedepth
01/31/2019, 9:50 PMVibhor
02/01/2019, 12:30 AMSELECT *
FROM (
SELECT *
FROM users
cross join chrome_extensions
using (UID)) foo
WHERE foo.identifier = 'blpcfgokakmgnkcojhhkbfbldkacnbeo' ;
WITH foo AS
(
SELECT *
FROM users
cross join chrome_extensions
using (UID))
SELECT *
FROM foo
WHERE identifier = 'blpcfgokakmgnkcojhhkbfbldkacnbeo';
gguli
02/01/2019, 10:57 AMVibhor
02/01/2019, 2:27 PMzwass
02/01/2019, 4:20 PMAll joins in SQLite are based on the cartesian product of the left and right-hand datasets.
There is no difference between the "INNER JOIN", "JOIN" and "," join operators. They are completely interchangeable in SQLite. The "CROSS JOIN" join operator produces the same result as the "INNER JOIN", "JOIN" and "," operators, but is handled differently by the query optimizer in that it prevents the query optimizer from reordering the tables in the join.
Vibhor
02/01/2019, 4:38 PMzwass
02/01/2019, 4:41 PMzwass
02/01/2019, 4:43 PMVibhor
02/01/2019, 4:45 PMzwass
02/01/2019, 4:49 PMAll joins in SQLite are based on the cartesian product of the left and right-hand datasets.
If the join-operator is "CROSS JOIN", "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, then the result of the join is simply the cartesian product of the left and right-hand datasets.
The special handling of CROSS JOIN is an SQLite-specific feature and is not a part of standard SQL.
Vibhor
02/01/2019, 4:51 PMzwass
02/01/2019, 4:54 PMgguli
02/01/2019, 5:10 PMzwass
02/01/2019, 5:11 PMchrome_extensions
actually are table valued functions with required constraints.gguli
02/01/2019, 6:03 PMzwass
02/01/2019, 6:04 PMgguli
02/01/2019, 6:06 PMzwass
02/01/2019, 6:07 PM