SQL JOINS – INNER – OUTER – CROSS

SQL JOINS are the fundamental name for retrieving data from multiple tables based on relationship. The more normalized data, the more joins and vice versa.

TYPES OF JOIN:

  1. INNER JOIN
  2. OUTER JOIN
    1. LEFT OUTER JOIN
    2. RIGHT OUTER JOIN
    3. FULL OUTER JOIN
  3. CROSS JOIN
  4. SELF JOIN

INNER JOIN:

  • An inner join is the default join in SQL server. If you do not specify the type of join then system assumes it an inner join.
  • Inner join only returns a result set with matching values between two tables A and B. Often it called as intersection between two tables.
  • Logical operators such as  <, >, =, <> can be used.

inner-join

 

LEFT OUTER JOIN:

  • Left outer join retrieves all the rows from table A (left) with matching and non matching records.
  • Non matching records from table B (right) shows NULL in the record set .
  • OUTER keyword is optional, you can use LEFT JOIN instead.
  • Logical operators such as              <, >, =, <> can be used.\

left-join

 

  • Only the non matching records from table A with a comparison to table B

left-join-distinct

 

RIGHT OUTER JOIN:

  • Right outer join is nothing but the reverse engineering of left outer join. It retrieves all the rows from table B (right), with matching and non matching records.
  • Non matching records from table A (left) shows NULL in the record set .
  • OUTER keyword is optional, you can use RIGHT JOIN instead.
  • Logical operators such as              <, >, =, <> can be used.

right-join

 

  • Only the non matching records from table B with a comparison to table A.

right-join-distinct

 

FULL OUTER JOIN:

  • Full outer join is an combination of LEFT and RIGHT OUTER JOIN.
  • It retrieves all the rows; matching and non matching between table A and B.
  • If there is no match, the missing side will show NULL in result set.
  • OUTER keyword is optional, you can use FULL JOIN instead.

full-join

 

  • If you need a result set where table A values are unique to table B and vice versa.

full-join-distinct

 

CROSS JOIN:

  • Cross join refers as a Cartesian product between two tables.
  • Table A (90 records) * Table B (5 records) = 450 (Total records)
  • Cross join does not need operators between two tables.

cross-join

 

SELF JOIN:

  • Self-join is just a term, not a key word of SQL server.
  • Self-join sounds simple and understood. It’s nothing but having a join between the same tables on self-reference columns.
  • The best example is an employee, manager relationship table. Where all of them have an employee id unique, and under manager multiple employees are working.
  • Self-join can be used with all types of join.

self-join-eg01

 

self-join-eg02

PS: If you want to understand more about the joins and other sql fundamentals, then drop an email to deviprasad@sqlindia.com or leave a reply on this post.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: