What does this MySQL statement do?

Question

INSERT IGNORE INTO `PREFIX_tab_lang` (`id_tab`, `id_lang`, `name`) (SELECT `id_tab`, id_lang, (SELECT tl.`name` FROM `PREFIX_tab_lang` tl WHERE tl.`id_lang` = (SELECT c.`value` FROM `PREFIX_configuration` c WHERE c.`name` = 'PS_LANG_DEFAULT' LIMIT 1) AND tl.`id_tab`=`PREFIX_tab`.`id_tab`) FROM `PREFIX_lang` CROSS JOIN `PREFIX_tab`); 

This is from an open source project and available documentation.

In particular, what does cross-join mean? I just used join / left join.

Answer 1

According to the MySQL documentation , this is basically a synonym for INNER JOIN , and INNER JOIN is the same as just JOIN (that is, " INNER " by default).

Answer 2

Cross Connection: http://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join

The query inserts the PREFIX_tab_lang results into PREFIX_tab_lang . The choice is just two cross-product columns. The third column - the name - actually comes from a completely different choice, which is also quite straightforward, except that one of them where is another choice.

In short, this is one of the worst queries I've ever seen. This pre-execution is probably horrible and should be replaced with a bit of transaction-protected code, or at least a stored procedure.

Answer 3

In fact, you can treat the following queries as synonyms in MySQL:

 SELECT * FROM Table1 CROSS JOIN Table2; SELECT * FROM Table1, Table2; SELECT * FROM Table1 INNER JOIN Table2; SELECT * FROM Table1 JOIN Table2; 

Test case:

 CREATE TABLE Table1 (id int, value varchar(10)); CREATE TABLE Table2 (id int, t1_id int); INSERT INTO Table1 VALUES (1, 'Value 1'); INSERT INTO Table1 VALUES (2, 'Value 2'); INSERT INTO Table1 VALUES (3, 'Value 3'); INSERT INTO Table1 VALUES (4, 'Value 4'); INSERT INTO Table2 VALUES (1, 1); INSERT INTO Table2 VALUES (2, 1); INSERT INTO Table2 VALUES (3, 2); INSERT INTO Table2 VALUES (4, 2); INSERT INTO Table2 VALUES (5, 2); INSERT INTO Table2 VALUES (6, 3); INSERT INTO Table2 VALUES (7, 4); INSERT INTO Table2 VALUES (8, 4); INSERT INTO Table2 VALUES (9, 4); 

All four queries will return the following result set:

 +------+---------+------+-------+ | id | value | id | t1_id | +------+---------+------+-------+ | 1 | Value 1 | 1 | 1 | | 2 | Value 2 | 1 | 1 | | 3 | Value 3 | 1 | 1 | | 4 | Value 4 | 1 | 1 | | 1 | Value 1 | 2 | 1 | | 2 | Value 2 | 2 | 1 | | 3 | Value 3 | 2 | 1 | | 4 | Value 4 | 2 | 1 | | 1 | Value 1 | 3 | 2 | | 2 | Value 2 | 3 | 2 | | 3 | Value 3 | 3 | 2 | | 4 | Value 4 | 3 | 2 | | 1 | Value 1 | 4 | 2 | | 2 | Value 2 | 4 | 2 | | 3 | Value 3 | 4 | 2 | | 4 | Value 4 | 4 | 2 | | 1 | Value 1 | 5 | 2 | | 2 | Value 2 | 5 | 2 | | 3 | Value 3 | 5 | 2 | | 4 | Value 4 | 5 | 2 | | 1 | Value 1 | 6 | 3 | | 2 | Value 2 | 6 | 3 | | 3 | Value 3 | 6 | 3 | | 4 | Value 4 | 6 | 3 | | 1 | Value 1 | 7 | 4 | | 2 | Value 2 | 7 | 4 | | 3 | Value 3 | 7 | 4 | | 4 | Value 4 | 7 | 4 | | 1 | Value 1 | 8 | 4 | | 2 | Value 2 | 8 | 4 | | 3 | Value 3 | 8 | 4 | | 4 | Value 4 | 8 | 4 | | 1 | Value 1 | 9 | 4 | | 2 | Value 2 | 9 | 4 | | 3 | Value 3 | 9 | 4 | | 4 | Value 4 | 9 | 4 | +------+---------+------+-------+ 36 rows in set (0.01 sec)