Last updated November 23, 2012. Created by drupalshrek on November 23, 2012.
Log in to edit this page.

Joins

To join against another table, use the join(), innerJoin(), leftJoin(), or rightJoin() methods, like so:

<?php
$query
= db_select('node', 'n');
$table_alias = $query->join('users', 'u', 'n.uid = u.uid AND u.uid = :uid', array(':uid' => 5));
?>

The above directive will add an INNER JOIN (the default join type) against the "user" table, which will get an alias of "u". The join will be ON the condition " n.uid = u.uid AND u.uid = :uid", where :uid has a value of 5. Note the use of a prepared statement fragment. That allows for the addition of variable join statements in a secure fashion. Never put a literal value or variable directly into a query fragment, just as literals and variables should never be placed into a static query directly (they can lead to SQL injection vulnerabilities). The innerJoin(), leftJoin(), and rightJoin() methods operate identically for their respective join types.

The return value of a join method is the alias of the table that was assigned. If an alias is specified it will be used except in the rare case that alias is already in use by a different table. In that case, the system will assign a different alias.

Note that in place of a literal such as 'user' for the table name, all of the join methods will accept a select query as their first argument. Example:

<?php
$query
= db_select('node', 'n');
$myselect = db_select('mytable')
  ->
fields('mytable')
  ->
condition('myfield', 'myvalue');
$alias = $query->join($myselect, 'myalias', 'n.nid = myalias.nid');
?>

Joins cannot be chained, so they have to be called separately (see Chaining). If you are chaining multiple functions together do it like this:

<?php
$query
= db_select('node', 'n');
$query->join('field_data_body', 'b', 'n.nid = b.entity_id');
$query
 
->fields('n', array('nid', 'title'))
  ->
condition('n.type', 'page')
  ->
condition('n.status', '1')
  ->
orderBy('n.created', 'DESC')
  ->
addTag('node_access');
?>

Note: whenever querying the node table you should make use of the "node_access" tag, see also the node access rights documentation about that.

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

<?php
      $query
=db_select('webform_submitted_data','wb');
     
$query->join('webform_submissions','w','wb.sid = w.sid');
     
$query->condition('w.uid','1')
                       ->
fields('w',array('sid','uid','nid'))
                       ->
fields('wb',array('data'));
    
$result= $query->execute();
    foreach(
$result as $record){
         print 
'<pre>';
        print 
$record->sid.$record->data;
    }
?>

Rakesh James

subquery from this example will kill your database:
this code gets latest node with nedded type and its url alias

<?php
$q_obj
= db_select('node', 'n');
$q_obj ->join('url_alias', 'ual', 'ual.source = CONCAT(:str, n.nid)', array(':str'=>'node/'));
$q_obj
   
->fields('n', array('title', 'created', 'nid'))
    ->
fields('ual', array('alias'))
    ->
condition('n.type', 'mynode_type', '=')
    ;
$q_obj->range(0, 1);
$q_obj->orderby('n.nid', 'DESC');
$res = $q_obj->execute()->fetchall();
?>

it executes in 9 ms on my computer, ok.

now try to do it with subquery ::

<?php
$q_sub
= db_select('url_alias');
$q_sub -> fields('url_alias', array('source', 'alias'));
$q_obj = db_select('node', 'n');
$q_obj ->join($q_sub, 'ual', 'ual.source = CONCAT(:str, n.nid)', array(':str'=>'node/'));
$q_obj
   
->fields('n', array('title', 'created', 'nid'))
    ->
fields('ual', array('alias'))
    ->
condition('n.type', 'mynode_type', '=')
    ;
$q_obj->range(0, 1);
$q_obj->orderby('n.nid', 'DESC');
$res = $q_obj->execute()->fetchall();
?>

want to know difference in speed? sure you want ,, so it 50 times slower than without subqueries.
yes, first its gets all data from url_alias table(it executes that sub query), and this is bad query, but its not so obvious at first look.