Joins in SQL:
Joins is used to combine rows from two or more tables, based on a related column between them.
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
(INNER) JOIN:
Returns records that have matching values in both tables
Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
LEFT (OUTER) JOIN:
Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN:
Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN:
Returns all records when there is a match in either left or right table
SQL Self Join
A self join is a regular join, but the table is joined with itself.
Self Join Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more SELECT statements.
UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the
number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG())
to group the result-set by one or more columns.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
The ALL operator:
returns a boolean value as a result
returns TRUE if ALL of the subquery values meet the condition
is used with SELECT, WHERE and HAVING statements
ALL Syntax with WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
The SQL SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
SELECT INTO Syntax
Copy all columns into a new table:
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Single Line Comments
Single line comments start with --.
Any text between -- and the end of the line will be ignored (will not be executed)
--- Select all: ---
SELECT * FROM Customers;
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a column it will allow only certain values for this column.
