SQL

There are different types of joins available in SQL:

INNER JOIN: This join returns rows when there is at least one match in both the tables.

LEFT OUTER JOIN: This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values. RIGHT OUTER JOIN: This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values. FULL OUTER JOIN: This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

SELF JOIN: is used to join a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the cartesian product of the sets of records from the two or more joined tables.  It equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement (i.e. no WHERE clause)

The following is the position of the HAVING clause in a query:

SELECT FROM WHERE GROUP BY HAVING ORDER BY

 Subqueries 

SQL> SELECT *

     FROM CUSTOMERS

     WHERE ID IN (SELECT ID

                  FROM CUSTOMERS

                  WHERE SALARY > 4500);

Subqueries with the INSERT Statement:

SQL> INSERT INTO CUSTOMERS_BKP

     SELECT * FROM CUSTOMERS

     WHERE ID IN (SELECT ID

                  FROM CUSTOMERS);

Subqueries with the UPDATE Statement:

SQL> UPDATE CUSTOMERS

     SET SALARY = SALARY * 0.25

     WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

                   WHERE AGE >= 27 );

Subqueries with the DELETE Statement:

SQL> DELETE FROM CUSTOMERS

     WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP

                   WHERE AGE > 27 );