Difference between revisions of "SQL"
Jump to navigation
Jump to search
(Created page with 'Category:SQL == Basics == === Select === The SELECT statement is used to assemble fields of different tables and queries to a new result. ==== Examples ==== * Selecting al...') |
|||
| Line 9: | Line 9: | ||
<syntaxhighlight lang=SQL> | <syntaxhighlight lang=SQL> | ||
SELECT * | SELECT * | ||
| − | FROM | + | FROM Table |
</syntaxhighlight> | </syntaxhighlight> | ||
* Selecting two fields from a table | * Selecting two fields from a table | ||
<syntaxhighlight lang=SQL> | <syntaxhighlight lang=SQL> | ||
| − | SELECT | + | SELECT Field1 |
| − | , | + | , Field2 |
| − | FROM | + | FROM Table |
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 31: | Line 31: | ||
== Joins == | == Joins == | ||
| + | |||
| + | === Inner Join === | ||
| + | With an inner join two tables can be linked together including all records in the resulting record set, which meet the specified criteria. | ||
| + | |||
| + | ==== Example ==== | ||
| + | * Select all fields from ''Table1'' and ''Table2'', where the value of ''Field'' equals in ''Table1'' and ''Table2''. | ||
| + | <syntaxhighlight lang=SQL> | ||
| + | SELECT Table1.* | ||
| + | , Table2.* | ||
| + | FROM Table1 | ||
| + | INNER JOIN Table2 | ||
| + | ON Table1.Field = Table2.Field | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | * The operator in the ON statement does not necessary have to be an equal sign. | ||
| + | * The left and right side of the ON statement are not limited to a plain field value; it is possible to calculate something here. | ||
| + | |||
| + | === Outer Joins === | ||
| + | Outer joins include all records from one table, while they are only displaying related fields of the other table, if matching records exist, otherwise the fields of the other table would be displayed with NULL values. | ||
| + | |||
| + | * Left Join: Display all records from the first table and only the matching records from the second table. | ||
| + | * Right Join: Display all records from the second table and only the matching records from the first table. | ||
| + | |||
| + | The "first" and "second" table is identified by the order in the SQL statement. Both outer joins are interchangeable if the order of tables is swapped or in other words: The result would be the same whether you choose to left join one table to the other or to right join the other table to the one. | ||
| + | |||
| + | ==== Example ==== | ||
| + | * Display all records from ''Table1'' and only the matching records from ''Table2'' | ||
| + | <syntaxhighlight lang=SQL> | ||
| + | SELECT Table1.* | ||
| + | , Table2.* | ||
| + | FROM Table1 | ||
| + | LEFT JOIN Table2 | ||
| + | ON Table1.Field = Table2.Field | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | * Display only the matching records from ''Table1'' and all records from ''Table2'' | ||
| + | <syntaxhighlight lang=SQL> | ||
| + | SELECT Table1.* | ||
| + | , Table2.* | ||
| + | FROM Table1 | ||
| + | RIGHT JOIN Table2 | ||
| + | ON Table1.Field = Table2.Field | ||
| + | </syntaxhighlight> | ||
Revision as of 16:26, 23 July 2010
Basics
Select
The SELECT statement is used to assemble fields of different tables and queries to a new result.
Examples
- Selecting all fields from a table
SELECT *
FROM Table
- Selecting two fields from a table
SELECT Field1
, Field2
FROM Table
Insert
- INSERT INTO
Update
- UPDATE
Delete
- DELETE
Joins
Inner Join
With an inner join two tables can be linked together including all records in the resulting record set, which meet the specified criteria.
Example
- Select all fields from Table1 and Table2, where the value of Field equals in Table1 and Table2.
SELECT Table1.*
, Table2.*
FROM Table1
INNER JOIN Table2
ON Table1.Field = Table2.Field
- The operator in the ON statement does not necessary have to be an equal sign.
- The left and right side of the ON statement are not limited to a plain field value; it is possible to calculate something here.
Outer Joins
Outer joins include all records from one table, while they are only displaying related fields of the other table, if matching records exist, otherwise the fields of the other table would be displayed with NULL values.
- Left Join: Display all records from the first table and only the matching records from the second table.
- Right Join: Display all records from the second table and only the matching records from the first table.
The "first" and "second" table is identified by the order in the SQL statement. Both outer joins are interchangeable if the order of tables is swapped or in other words: The result would be the same whether you choose to left join one table to the other or to right join the other table to the one.
Example
- Display all records from Table1 and only the matching records from Table2
SELECT Table1.*
, Table2.*
FROM Table1
LEFT JOIN Table2
ON Table1.Field = Table2.Field
- Display only the matching records from Table1 and all records from Table2
SELECT Table1.*
, Table2.*
FROM Table1
RIGHT JOIN Table2
ON Table1.Field = Table2.Field