Skip to main content

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 




Comments

Popular posts from this blog

Get The field values of node in Drupal 8

use Drupal \ node \ NodeInterface ; /** * Implements hook_ENTITY_TYPE_insert() for node entities. * * This tests saving a node on node insert. * * @see \Drupal\node\Tests\NodeSaveTest::testNodeSaveOnInsert() */ function node_test_node_insert ( NodeInterface $node ) { // Set the node title to the node ID and save. if ( $node - > getTitle ( ) == 'new' ) { $node - > setTitle ( 'Node ' . $node - > id ( ) ) ; $node - > setNewRevision ( FALSE ) ; $node - > save ( ) ; } } Now There is so many functions are there to get the values, For All the functions available visit the API code, https://api.drupal.org/api/drupal/core%21modules%21node%21src%21NodeInterface.php/interface/NodeInterface/8.2.x Some of as below, Node edit form, Drupal 8 Automatically Load the whole object no need to load the entity like below, if ($event->getFormId() == 'node_alexa_audio_clips_edit_form') { $node = \Drupal::ro...

Drupal Setup with docker

Option 1:  docker pull mysql docker pull phpmyadmin docker pull drupal docker run -d --name MysqlClient -e MYSQL_ROOT_PASSWORD=drupal mysql Download the phpmyadmin image and Setup Container: docker run --name phpmyadmin -d --link MysqlClient:db -p 8083:80 phpmyadmin Download the Drupal image and Setup Container: docker run -d --name dweb2 --link MysqlClient -p 8082:80 -e MYSQL_USER:root -e MYSQL_PASSWORD:drupal drupal   We are using docker4 drupal and following below URL https://wodby.com/docs/1.0/stacks/drupal/local/ From Mount my codebase Download and unpack docker4drupal.tar.gz with hidden files in a new directory, also get the drupal code with in web directory composer create - project drupal / recommended - project my_site_name_dir It means with in new directory we have docker files and drupal files also has web folder for drupal. now run docker-compose up -d Now PROJECT_BASE_URL is mentioned in .env file open this url in the web with port, it will open the drupal page, ...