1. LEFT OUTER JOIN: In left outer join all rows from the table on the left side of the LEFT OUTER JOIN keyword is returned, and the matching rows from the table specified on the right side are returned the result set.

2. RIGHT OUTER JOIN: In right outer join all rows from the table on the right side of the RIGHT OUTER JOIN keyword are returned, and the matching rows from the table specified on the left side are returned is the result set.

3. FULL OUTER JOIN: It is a combination of left outer join and right outer join. This outer join returns all the matching and non-matching rows from both tables. Whilst, the matching records are displayed only once.

6.3 CROSS JOIN

Also known as the Cartesian Product between two tables joins each row from one table with each row of another table. The rows in the result set is the count of rows in the first table times the count of rows in the second table.

6.4 EQUI JOIN

An Equi join is the same as inner join and joins tables with the help of foreign key except this join is used to display all columns from both tables.

6.5 SELF JOIN

In self join, a table is joined with itself. As a result, one row is in a table correlates with other rows in the same table. In this join, a table name is mentioned twice in the query. Hence, to differentiate the two instances of a single table, the table is given two aliases. Syntax:

7.2 Using EXISTS Keyword

EXISTS clause is used with subquery to check if a set of records exists.

TRUE value is returned by the subquery in case if the subquery returns any row.

Syntax:

SELECT column, column
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition)

7.3 Using Nested Subqueries

A subquery can contain more than one subqueries. Subqueries are used when the condition of a query is dependent on the result of another query, which is, in turn, is dependent on the result of another subquery.

9.3 PIVOT Clause

The PIVOT operator is used to transform a set of columns into values, PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

Simran Kaur Arora

Simran, born in Delhi, did her schooling and graduation from India in Computer Science. Curious and passionate for technology urged her to study for an MS in same from the renowned Silicon Valley, California USA. Graduated in 2017, she flew back to India and now works for hackr.io as a freelance technical writer. View all posts by Simran Kaur Arora

Related Posts

Your email address will not be published. Required fields are marked *

Name*

Email*

Comment

Who we are

Hackr.io is a programming and design community website to find the best online courses & tutorials. All the tutorials are submitted and upvoted by the community members so you get the best recommendation.