Institute Academy

SQL Self JOIN A self JOIN occurs when a table takes a 'selfie'. A self JOIN is a regular join but the table is joined with itself. This can be useful when modeling hierarchies. They are also useful for comparisons within a table. The SQL Self JOIN syntax The general syntax is: SELECT column-names FROM table-name T1 JOIN table-name T2 WHERE condition T1 and T2 are different table aliases for the same table  SQL Self JOIN Examples

SQL FULL JOIN Statement FULL JOIN returns all matching records from both tables whether the other table matches or not. FULL JOIN can potentially return very large data-sets. FULL JOIN and FULL OUTER JOIN are the same. The SQL FULL JOIN syntax The general syntax is: SELECT column-names FROM table-name1 FULL JOIN table-name2 ON column-name1 = column-name2 WHERE condition The general FULL OUTER JOIN syntax is: SELECT column-names FROM table-name1 FULL OUTER JOIN table-name2 ON column-name1 = column-name2 WHERE condition SQL FULL JOIN Examples

SQL RIGHT JOIN RIGHT JOIN performs a join starting with the second (right-most) table and then any matching first (left-most) table records. RIGHT JOIN and RIGHT OUTER JOIN are the same. The SQL RIGHT JOIN syntax The general syntax is: SELECT column-names FROM table-name1 RIGHT JOIN table-name2 ON column-name1 = column-name2 WHERE condition The general RIGHT OUTER JOIN syntax is: SELECT column-names FROM table-name1 RIGHT OUTER JOIN table-name2 ON column-name1 = column-name2 WHERE condition SQL RIGHT JOIN Example  1) Example of Right outer join  mysql> select customers.customername, orders.orderid from customers right join orders on customers.customerid = orders.customerid; +---------------+---------+ | customername  | orderid | +---------------+---------+ | Mike Wooksahi | 10308   | | NULL          | 10309   | | NULL          | 10310   | +---------------+---------+ 3 rows in set (0.00 sec) 2) Example o

SQL LEFT JOIN LEFT JOIN performs a join starting with the first (left-most) table and then any matching second (right-most) table records. LEFT JOIN and LEFT OUTER JOIN are the same. The SQL LEFT JOIN syntax The general syntax is: SELECT column-names FROM table-name1 LEFT JOIN table-name2 ON column-name1 = column-name2 WHERE condition SQL LEFT JOIN Examples 1) Example of Left Outer join mysql> select customers.customername, orders.orderid from customers left join orders on customers.customerid = orders.customerid; +---------------+---------+ | customername  | orderid | +---------------+---------+ | Mike Wooksahi | 10308   | | Subham Ball   | NULL    | | jon Wooksahi  | NULL    | +---------------+---------+ 3 rows in set (0.00 sec) 2) Example of Left Outer join with order by Clause mysql> select customers.customername, orders.orderid from customers left join orders on customers.customerid = orders.customerid order

SQL JOIN A SQL JOIN combines records from two tables. A JOIN locates related column values in the two tables. A query can contain zero, one, or multiple JOIN operations. INNER JOIN is the same as JOIN; the keyword INNER is optional. Different types of JOINs (INNER) JOIN: Select records that have matching values in both tables. LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records. RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records. FULL (OUTER) JOIN: Selects all records that match either left or right table records.     The SQL JOIN syntax The general syntax is: SELECT column-names FROM table-name1 JOIN table-name2 ON column-name1 = column-name2 WHERE condition The general syntax with INNER is: SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition SQL JOIN

SQL Alias An Alias is a shorthand for a table or column name. Aliases reduce the amount of typing required to enter a query. Complex queries with aliases are generally easier to read. Aliases are useful with JOINs and aggregates: SUM, COUNT, etc. An Alias only exists for the duration of the query. Alias Column Syntax SELECT column_name AS alias_name FROM table_name; Alias Table Syntax   SELECT column_name(s) FROM table_name AS alias_name; SQL Alias Examples 1) Example of Alias column    mysql> select name as alias_name from person; +-----------------+ | alias_name      | +-----------------+ | subham ball     | | jit             | | sajal           | | sourav biswas   | | sajal           | | chayan das      | | shantanu biswas | | sudip saha      | | debo            | | NULL            | | NULL            | | mono            | | monojit         | +-----------------+ 13 rows in set (0.00 sec) 2) Example of Alias Table 

SQL HAVING Clause HAVING filters records that work on summarized GROUP BY results. HAVING applies to summarized group records, whereas WHERE applies to individual records. Only the groups that meet the HAVING criteria will be returned. HAVING requires that a GROUP BY clause is present. WHERE and HAVING can be in the same query. The SQL HAVING syntax The general syntax is: SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition The general syntax with ORDER BY is: SELECT column-names FROM table-name WHERE condition GROUP BY column-names HAVING condition ORDER BY column-names SQL GROUP BY Examples 1) Example of having Clause  without order by mysql> select name,count(age) from person group by name having count(age>5); +-----------------+------------+ | name            | count(age) | +-----------------+------------+ | chayan das      |          1 | | jit             |