The CARTESIAN JOIN or CROSS JOIN return the data by joining the every row of one table to every row of another table i.e it returns the Cartesian product of two tables. Syntax: SELECT columnList FROM table1 t1, table1 t2; Example: SELECT * FROM PERSONS FULL OUTER JOIN ORDERS ON PERSONS.P_ID = ORDERS.PERSON_ID;SELECT * FROM … Read more

The SELF JOIN is used to join the table to itself that why it is known as SELF JOIN. Syntax: SELECT columnList FROM table1 t1, table1 t2 WHERE t1.commonColumn = t2. commonColumn; Example: SELECT emp1.EMP_NAME FROM EMPLOYEE emp1, EMPLOYEE emp2 WHERE emp1.AGE = emp2.AGE;SELECT emp1.EMP_NAME FROM EMPLOYEE emp1, EMPLOYEE emp2 WHERE emp1.AGE = emp2.AGE; Output: … Read more

The FULL OUTER JOIN returns the result of the combination of left and right outer joins. Syntax: SELECT columnList FROM table1 FULL OUTER JOIN table2 ON table1.columnName = table2.columnName; Example: SELECT P_ID, NAME, AMOUNT   FROM PERSONS   FULL OUTER JOIN ORDERS   ON PERSONS.P_ID = ORDERS.PERSON_ID;SELECT P_ID, NAME, AMOUNT FROM PERSONS FULL OUTER JOIN … Read more

The RIGHT OUTER JOIN returns the all records of right table and matching records of left table. When no match is found left table columns will be return with the null values. Syntax: SELECT columnList FROM table1 RIGHT OUTER JOIN table2 ON table1.columnName = table2.columnName; or SELECT columnList FROM table1 RIGHT JOIN table2 ON table1.columnName … Read more

The LEFT OUTER JOIN returns the all records of left table and matching records of right table. When no match is found right table columns will be return with the null values. Syntax: SELECT columnList FROM table1 LEFT OUTER JOIN table2 ON table1.columnName = table2.columnName; or SELECT columnList FROM table1 LEFT JOIN table2 ON table1.columnName … Read more

The INNER JOIN returns the all records from the both tables for which the join condition is true. It is also known as EQUIJOIN. Syntax: SELECT columnList FROM table1 INNER JOIN table2 ON table1.columnName = table2.columnName; or SELECT columnList FROM table1 JOIN table2 ON table1.columnName = table2.columnName; Example: SELECT P_ID, NAME, AMOUNT   FROM PERSONS … Read more

The JOIN are used to combine the records from two or more tables. Types of SQL JOIN: SQL INNER JOIN. SQL LEFT OUTER JOIN. SQL RIGHT OUTER JOIN. SQL FULL OUTER JOIN. SQL SELF JOIN. SQL CARTESIAN JOIN or CROSS JOIN.   Next Topic: SQL INNER JOIN with example. Previous Topic: SQL HAVING clause with … Read more

The HAVING clause is used with the GROUP BY clause and filter the groups created by the GROUP BY clause. Syntax: SELECT column1, column2,…, columnN FROM tableName WHERE[conditions] GROUP BY column1, column2 …HAVING[conditions]; Example: SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY EMP_NAME HAVING Count(EMP_NAME) >= 2;SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY EMP_NAME HAVING Count(EMP_NAME) … Read more

The GROUP BY clause is used to group the identical data by one or more columns. It is used with the aggregate functions in the select statement. Syntax: SELECT column1, column2,…, columnN FROM tableName WHERE[conditions] GROUP BY column1, column2 …;  Example: SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY EMP_NAME;SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE GROUP BY … Read more

The ORDER BY Clause is used to sort the results either in ascending or descending order based on one or more columns. Oracle and some other database sorts query results in ascending order by default. Syntax: SELECT * FROM tableName [WHERE condition] [ORDER BY column1, column2,…,columnN] [ASC | DESC]; Where ASC keyword is used for … Read more