Sql joins: Difference between revisions
From wikinotes
m (Will moved page Sql: joins to Sql joins without leaving a redirect) |
(→Joins) |
||
(One intermediate revision by the same user not shown) | |||
Line 1: | Line 1: | ||
= | = Joins = | ||
< | <blockquote> | ||
== Overview == | |||
<blockquote> | |||
Joins let you | |||
* query rows from multiple tables at once | |||
* filter your table based on an association to another table | |||
<syntaxhighlight lang="MySQL"> | |||
SELECT * | SELECT * # select all rows from 'users' and from 'department_users' tables | ||
FROM users | |||
INNER JOIN department_users # only return subset of rows where the 'ON' condition is met | |||
ON department_users.user_id = users.id # what determines a match? multiple conditions can be defined | |||
</syntaxhighlight> | |||
</blockquote><!-- Overview --> | |||
== Types == | |||
<blockquote> | |||
There are generally at least 3x types of joins. | |||
* <code>INNER JOIN</code> - rows where match-condition is met | |||
* <code>LEFT JOIN</code> - all rows in src-table, columns in join-table without a match are all NULL | |||
* <code>RIGHT JION</code> - all rows in join-table, columns in src-tabl without a match are all NULL | |||
</blockquote><!-- Types --> | |||
</ | |||
= | == Subqueries == | ||
< | <blockquote> | ||
You can also join on a nested query.<br> | |||
See [[sql queries]] for an example. | |||
</blockquote><!-- Subqueries --> | |||
< | </blockquote><!-- Joins --> | ||
< | |||
Latest revision as of 19:19, 19 September 2021
Joins
Overview
Joins let you
- query rows from multiple tables at once
- filter your table based on an association to another table
SELECT * # select all rows from 'users' and from 'department_users' tables FROM users INNER JOIN department_users # only return subset of rows where the 'ON' condition is met ON department_users.user_id = users.id # what determines a match? multiple conditions can be definedTypes
There are generally at least 3x types of joins.
INNER JOIN
- rows where match-condition is metLEFT JOIN
- all rows in src-table, columns in join-table without a match are all NULLRIGHT JION
- all rows in join-table, columns in src-tabl without a match are all NULLSubqueries
You can also join on a nested query.
See sql queries for an example.