MYSQL Table Joining

leftJoin == LEFT OUTER JOIN
join == innerjoin
right join == RIGHT OUTER JOIN


Print raw SQL queries for debugging

$connection = \Drupal::service('database');
	$query = $connection->select('node', 'node');
	$query->fields('node', ['nid'])
	->condition('node.type', 'page')
	// Debug.
	dump($query->__toString());

Which Table is in Left and whilch is in Right

Lets suppose there are two tables, emp and deptt

 SELECT emp.name,emp.no,d_name FROM emp left outer join deptt on (emp.dept_no = dept.dept_no)

it left Join , and in left side table is emp and right side table is deptt.

When we perform the left join it return the emp table.

Type of Joins : 

1  - Inner join (Natural join)(join)

      1.1 - Natural join 

       1.2 - Conditional join or theta join

       1.3 - Equi join

2 - Outer join:
      1.1 - left outer join == left join
      1.2 - right outer join == right join
      1.3 - full outer join == full join
3 - Cross join
4 - Self join

SQL INNER JOIN Keyword

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL FULL OUTER JOIN Keyword

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Full join will return = (No. of Rows by LEFT OUTER JOIN) + (No. of Rows by RIGHT OUTER JOIN) - (No. of Rows by INNER JOIN)

I.e    = 10+ 20- (5) = 15

CROSS JOIN :

 it will join each record from one table to each record to another table .

Like if table a have 5 records and table b also have 5 records it will return 5*5 = 25 records