Sql queries: Difference between revisions
From wikinotes
Line 29: | Line 29: | ||
<blockquote> | <blockquote> | ||
== Nested Queries == | == Nested Queries == | ||
<blockquote> | |||
You can nest SQL queries and treat them as tables.<br> | You can nest SQL queries and treat them as tables.<br> | ||
The example below finds <code>user.id</code> values that are not present in the 1:many <code>user_departments</code> table. | The example below finds <code>user.id</code> values that are not present in the 1:many <code>user_departments</code> table. | ||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT * | SELECT users.* | ||
FROM users | FROM users | ||
LEFT JOIN ( | LEFT JOIN ( | ||
SELECT DISTINCT users.id AS user_id | SELECT DISTINCT users.id AS user_id | ||
Line 39: | Line 41: | ||
INNER JOIN users ON users.id = user_departments.user_id | INNER JOIN users ON users.id = user_departments.user_id | ||
) AS users_with_departments | ) AS users_with_departments | ||
WHERE users_with_departments.user_id IS NULL; | WHERE users_with_departments.user_id IS NULL; | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Nested Queries --> | |||
== Dynamically Defined Tables == | == Dynamically Defined Tables == |
Revision as of 18:56, 19 September 2021
Example
SELECT * FROM users WHERE username = "dvader" AND age > 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 name
- Use sql joins to associate the queried table's data to other tables.
- Use sql comparison operators in your
WHERE
statement to determine what you want to select.
Techniques
Nested Queries
You can nest SQL queries and treat them as tables.
The example below findsuser.id
values that are not present in the 1:manyuser_departments
table.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 WHERE users_with_departments.user_id IS NULL;Dynamically 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