Please enable JavaScript.
Coggle requires JavaScript to display documents.
SQL - Coggle Diagram
SQL
DML
SUBQUERY
SCALAR: subquery returns a single value.
E.g. select (select s1 from t2) from t1
Note
:warning: In this case, the select can return only column and a single value at that
Subquery in where: select max(Salary) as SecondHighestSalary from Employee
where Salary < (select Max(Salary) from Employee)
Row Subquery: select * from t5 where row(col1,col2) = (select col1, col2 from t6 where s1 = 2);
-
-
Derived Table When a subquery follows the "from" then this becomes a derived table and a table name has to be provided
JOIN
Inner Join - Select records present in both tables
select table1.col1, table2.col2...
from table1 inner join table2 on
table1.common_field = table2.common_field
Left Join - Select all records that are in the left table and matched records in the right. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.
Records only in left table:
select table1.col1, table2.col1...
from table1 left join table2
on table1.common_field = table2.common_field
where table2.common_field is null
Classic left join:
select table1.col1, table2.col1....
from table1 left join table2 on
table1.common_field = table2.common_field
Right Join - select all records that are in the right table and records in the left table for which there is a match. For records in the left table with no match, the values are displayed as nulls
Full Outer Join - combination of records in both tables. Different from a cross join in the sense it is a combination of matching records and non matching records and not a join of each record with another. Not supported in MySQL. Can be effected by doing a Left Join and then doing a union all with a right join where only tables in right table are selected.
UNION
union - combines results from multiple selects into a single result set. Removes duplicate values by default
union distinct - same as union, makes it explicit
-
SELECT
select | col1, col2, col3
from table_name
where [cnoditions]
group by col1,col2...
having [conditions]
order by col1, col2..
*limit [int]
Order of Execution
1 from - choose and join tables to get base data
2 where - filters the base data
3 group by - aggregates the data
4 having - filters the aggregated data
5 select - identifies columns
6 order by - sorts the final data
7 limit - limits the returned data to a row count
DELETE
-
Multi Table: delete table1, table2 from table1 join table2 on table1.col = table2.col where condition
DDL
TABLE
create table table_name
(col_name datatype [not null | null] [default value],
(col_name datatype [not null | null] [default value],
....,
primary key col_name
)
-
-
-
-
-
-