Sql queries: Difference between revisions
From wikinotes
(→SELECT) |
m (Will moved page Sql: queries to Sql queries without leaving a redirect) |
(No difference)
|
Revision as of 18:39, 19 September 2021
Example
SELECT * FROM users WHERE username = "dvader" AND age > 40;
Query Components
SELECT
Determines what columns you want to return.
SELECT * # select all columns SELECT col1, col2 # select col1, and col2 only SELECT col1 AS foo # rename column in results tableDISTINCT
Distinct only returns one result per unique value of N
SELECT DISTINCT username FROM users WHERE last_name = "skywalker";LIMIT
LIMIT lets you choose only the first N results
SELECT * FROM users LIMIT 10ORDER BY
SELECT * FROM users ORDER BY first_name ASC;JOINS
See sql: joins
Techniques
Nested Queries
SELECT user_Id FROM ( SELECT DISTINCT d.user_Id , CONCAT(u.firstName, " ", u.lastName) user_fullName FROM userDepartmentTable d INNER JOIN userTable AS u ON d.user_Id = u.user_Id WHERE d.department_Id = 2 ) u WHERE u.user_fullName REGEXP 'andr%';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