On 2008 October 29 (Wed) 03:26:45pm PDT, James Sheridan <[EMAIL PROTECTED]>
wrote:
> Related addendum:
>
> In reading it appears that MySQL treats "," as a CROSS JOIN and implements it
> effectively as an INNER JOIN.
> a) Is this correct?
Not really, no.
MySQL treats "," as a CROSS JOIN, which is consistent with everyone else.
Now, in "standard" SQL, when you use "INNER JOIN", you have to use a
join condition. (A join condition is something like "ON tasks.id =
projects.task_id" or "USING (id)".) When you use "CROSS JOIN", you
can't use a join condition.
In MySQL, you can always substitute "CROSS JOIN" for "INNER JOIN" and
vice versa. So if you say "INNER JOIN" but don't give a join condition,
MySQL assumes you mean "CROSS JOIN", and if you use "CROSS JOIN" but
give a join condition, MySQL acts like you meant "INNER JOIN".
So it's not a matter of implementation. It's just syntax.
> and b) Is Sqlite acting the same or treating it as a true CROSS JOIN?
SQLite treats "," as a CROSS JOIN, the same as MySQL.
> I suspect I'm writing things that I've used with MySQL for a while that are
> just
> not acting the same on Sqlite. That's fine, but I do want to know that if so
> :)
In the example you gave, both MySQL and SQLite are going to return 0
matches from a cross join with a table with 0 rows. That's how cross
joins work. It sounds like you want a LEFT JOIN.
mysql> CREATE TABLE Query (
-> id INTEGER NOT NULL,
-> creatorID INTEGER NOT NULL,
-> ownerID INTEGER NOT NULL,
-> type TEXT NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE UserQuery (
-> userID INTEGER NOT NULL,
-> queryID INTEGER NOT NULL
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO Query (id, creatorID, ownerID, type) VALUES (1, 2, 3, 'a');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT Q.*
-> FROM Query Q,
-> UserQuery UQ
-> WHERE Q.type = 'a' OR
-> (Q.id = UQ.queryID AND
-> UQ.userID = '1');
Empty set (0.01 sec)
mysql> SELECT Q.*
-> FROM Query Q
-> LEFT JOIN UserQuery UQ
-> ON (Q.id = UQ.queryID)
-> WHERE Q.type = 'a' OR
-> UQ.userID = '1';
+----+-----------+---------+------+
| id | creatorID | ownerID | type |
+----+-----------+---------+------+
| 1 | 2 | 3 | a |
+----+-----------+---------+------+
1 row in set (0.00 sec)
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
