Sql queries: Difference between revisions
From wikinotes
(→JOINS) |
|||
(21 intermediate revisions by the same user not shown) | |||
Line 3: | Line 3: | ||
<blockquote> | <blockquote> | ||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * | SELECT * # select all rows | ||
FROM users | FROM users # from users table | ||
WHERE username = "dvader" | WHERE username = "dvader" # where the 'username' column value is 'dvader' | ||
AND age > 40; | AND age > 40; # and the 'age' column value is above 40 | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Example --> | </blockquote><!-- Example --> | ||
Line 12: | Line 12: | ||
= Query Components = | = Query Components = | ||
<blockquote> | <blockquote> | ||
SELECT which columns you'd like to see, and how you'd like them to be presented (order, max results, etc). | |||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * | SELECT * # select all columns | ||
SELECT | SELECT name, age FROM users; # select name, and age only | ||
SELECT | SELECT name AS userame FROM users; # rename column in results table | ||
SELECT DISTINCT name FROM users; # select 1x row for each unique name in users | |||
SELECT * FROM users LIMIT 10; # only select first 10 results | |||
SELECT * FROM users ORDER BY name ASC # sort results in ascending order by name | |||
</syntaxhighlight> | </syntaxhighlight> | ||
= | After your selection you may: | ||
* use [[sql joins]] to associate the queried table's data to other tables.<br>''(ex: find users assigned to a project)''<br><br> | |||
* use [[sql aggregate functions]] to operate on groups of rows<br>''(ex: find average age of users in users table)''<br><br> | |||
* use [[sql comparison operators]] in your <code>WHERE</code> statement to determine what you want to select.<br>''(ex: find users whose name starts with an 'A')'' | |||
</blockquote><!-- Query Components --> | |||
= Techniques = | |||
<blockquote> | <blockquote> | ||
== Nested Queries == | |||
< | <blockquote> | ||
You can nest SQL queries and treat them as tables. | |||
The example below finds <code>users.id</code> values<br> | |||
</ | that are not present in the many:many <code>user_departments</code> table,<br> | ||
</ | by joining a user table query to itself. | ||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * | SELECT users.* | ||
FROM users | FROM users | ||
LEFT JOIN ( | |||
SELECT DISTINCT users.id AS user_id | |||
FROM user_departments | |||
INNER JOIN users ON users.id = user_departments.user_id | |||
) AS users_with_departments | |||
ON users_with_departments.user_id = users.id | |||
WHERE users_with_departments.user_id IS NULL; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
You can also nest a query within a <code>WHERE</code> condition.<br> | |||
< | Joined tables are accessible within this nested query. | ||
</ | |||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * FROM users | SELECT * | ||
FROM users | |||
LEFT JOINS assignments ON assignments.user_id = users.id | |||
WHERE ((SELECT COUNT(1) FROM users WHERE assignments.active = 1) > 0) # the outer 'assignmens' is in scope here | |||
SELECT | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- | </blockquote><!-- Nested Queries --> | ||
= | == Dynamically Defined Tables == | ||
<blockquote> | <blockquote> | ||
Some databases do not implement the SQL IN operator.<br> | Some databases do not implement the SQL IN operator.<br> | ||
Instead, you can use VALUES. | Instead, you can use VALUES. | ||
Line 122: | Line 88: | ||
</source> | </source> | ||
</blockquote><!-- Techniques --> | </blockquote><!-- Techniques --> | ||
</blockquote><!-- Dynamically Defined Tables --> | |||
< | |||
< |
Latest revision as of 20:41, 9 August 2022
Example
SELECT * # select all rows FROM users # from users table WHERE username = "dvader" # where the 'username' column value is 'dvader' AND age > 40; # and the 'age' column value is above 40
Query Components
SELECT which columns you'd like to see, and how you'd like them to be presented (order, max results, etc).
SELECT * # select all columns SELECT name, age FROM users; # select name, and age only SELECT name AS userame FROM users; # rename column in results table SELECT DISTINCT name FROM users; # select 1x row for each unique name in users SELECT * FROM users LIMIT 10; # only select first 10 results SELECT * FROM users ORDER BY name ASC # sort results in ascending order by nameAfter your selection you may:
- use sql joins to associate the queried table's data to other tables.
(ex: find users assigned to a project)- use sql aggregate functions to operate on groups of rows
(ex: find average age of users in users table)- use sql comparison operators in your
WHERE
statement to determine what you want to select.
(ex: find users whose name starts with an 'A')
Techniques
Nested Queries
You can nest SQL queries and treat them as tables.
The example below finds
users.id
values
that are not present in the many:manyuser_departments
table,
by joining a user table query to itself.SELECT users.* FROM users LEFT JOIN ( SELECT DISTINCT users.id AS user_id FROM user_departments INNER JOIN users ON users.id = user_departments.user_id ) AS users_with_departments ON users_with_departments.user_id = users.id WHERE users_with_departments.user_id IS NULL;You can also nest a query within a
WHERE
condition.
Joined tables are accessible within this nested query.SELECT * FROM users LEFT JOINS assignments ON assignments.user_id = users.id WHERE ((SELECT COUNT(1) FROM users WHERE assignments.active = 1) > 0) # the outer 'assignmens' is in scope hereDynamically Defined Tables
Some databases do not implement the SQL IN operator.
Instead, you can use VALUES.SELECT * FROM ( VALUES (1), (2) ) foo(id);Most useful within a join
SELECT * FROM ( VALUES (1), (2) ) foo(id) INNER JOIN ( (VALUES (1)) bar(id) ) ON foo.id = bar.id