Portable SQL/Statements/Data retrieval/Join syntax - Revision history

Nose: removed backlink

2008-06-22T13:07:50Z

removed backlink

75.71.77.217: /* Left join */

2008-04-28T06:39:30Z

‎Left join

Nose: explainations

2008-04-05T12:37:59Z

explainations

Nose: initial

2007-09-22T10:49:21Z

initial

New page

SQL supports several ways to join the rows of two or more tables.

== Inner join ==

SELECT ''column-list''
FROM ''table1''
[INNER] JOIN ''table2'' ON ''table1''.''key'' = ''table2''.''key''
WHERE ''conditions''

== Left join ==

SELECT ''column-list''
FROM ''table1''
LEFT JOIN ''table2'' ON ''table1''.''key'' = ''table2''.''key''
WHERE ''conditions''

----
Back to [[Portable_SQL/Statements/Data_retrieval|Overview]]

← Older revision		Revision as of 13:07, 22 June 2008
Line 39:		Line 39:

	This isn't perfect as MySQL doesn't use indices any more after the OR composition so this part should be placed at the end of the WHERE clause.		This isn't perfect as MySQL doesn't use indices any more after the OR composition so this part should be placed at the end of the WHERE clause.
−
−
−	~~----~~
−	~~Back to [[Portable_SQL/Statements/Data_retrieval\|Overview]]~~

@@ Line 29: / Line 29: @@
   WHERE ''conditions''
-Contrary to inner joins, left joins even return rows if there are now matching rows in the second table. In this case, the columns from the second table in the column list are filled with NULL values.
+Contrary to inner joins, left joins even return rows if there are not matching rows in the second table. In this case, the columns from the second table in the column list are filled with NULL values.
 If you want to limit the the joined rows from the second table, it's very convenient to add the conditions in the ON clause of the JOIN. At least MySQL 4 seems to be buggy in this case and sometimes returns no rows at all. As a workaround you may add the condition to the WHERE clause but have to check for NULL values first:

@@ Line 1: / Line 1: @@
-SQL supports several ways to join the rows of two or more tables.
+SQL supports several ways to join the rows of two or more tables. In the examples of the following sections are only two tables used for simplicity but in everydays use it's often required to join more tables. The principle is the same as in the examples.
 == Inner join ==
   SELECT ''column-list''
-  FROM ''table1''
+  FROM ''table1'' AS t1
-  [INNER] JOIN ''table2'' ON ''table1''.''key'' = ''table2''.''key''
+  [INNER] JOIN ''table2'' AS t2 ON ( t1.''key'' = t2.''key'' )
   WHERE ''conditions''
 == Left join ==
   SELECT ''column-list''
-  FROM ''table1''
+  FROM ''table1'' AS t1
-  LEFT JOIN ''table2'' ON ''table1''.''key'' = ''table2''.''key''
+  LEFT JOIN ''table2'' AS t2 ON ( t1.''key'' = t2.''key'' )
   WHERE ''conditions''
 ----
 Back to [[Portable_SQL/Statements/Data_retrieval|Overview]]