Institute Academy

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             |         

SQL GROUP BY Clause The GROUP BY clause groups records into summary rows. GROUP BY returns one records for each group. GROUP BY typically also involves aggregates: COUNT, MAX, SUM, AVG, etc. GROUP BY can group by one or more columns. The SQL GROUP BY syntax The general syntax is: SELECT column-name FROM table-name WHERE condition GROUP BY column-names The general syntax with ORDER BY is: SELECT column-names FROM table-name WHERE condition GROUP BY column-name ORDER BY column-names SQL GROUP BY Examples  1) Example of Group By Clause mysql> select name from person where age>20 group by name; +-----------------+ | name            | +-----------------+ | chayan das      | | monojit         | | sajal           | | shantanu biswas | | sourav biswas   | | subham ball     | | sudip saha      | +-----------------+ 7 rows in set (0.00 sec) 2) Example of Group By Clause with  ORDER BY mysql> select

SQL IS NULL Clause NULL is a special value that signifies 'no value'. Comparing a column to NULL using the = operator is undefined. Instead, use WHERE IS NULL or WHERE IS NOT NULL. The SQL WHERE IS NULL syntax The general syntax is: SELECT column-names FROM table-name WHERE column-name IS NULL The general not null syntax is: SELECT column-names FROM table-name WHERE column-name IS NOT NULL SQL WHERE IS NULL Examples  1) Example of is null Clause  mysql> select name from person where age is null; +------+ | name | +------+ | debo | | NULL | | NULL | +------+ 3 rows in set (0.00 sec) 2) Example of Is not null  mysql> select name from person where age is not null; +-----------------+ | name            | +-----------------+ | subham ball     | | jit             | | sajal           | | sourav biswas   | | sajal           | | chayan das      | | shantanu biswas | | sudip saha      | | mono            | | m