In this chapter, you will learn how we can implement the JOIN query in a Node.js application on the tables in a MySQL database. A relational database consists of multiple related tables linking together using common columns, which are known as foreign key columns. MySQL supports JOIN query with which it is possible to extract and use data from more than one tables in a Node.js application. As in the earlier examples, we should call the mysql.query() method, and pass a SQL query string to mysql.query() method, the string should represent the MySQL JOIN query syntax.

We shall use the following tables in this chapter −

Members table

CREATE TABLE members (
   member_id INT AUTO_INCREMENT,
   name VARCHAR(100),
   PRIMARY KEY (member_id)
);

INSERT INTO members(name)
VALUES(''John''),(''Jane''),(''Mary''),(''David''),(''Amelia'');

Data in members table −

mysql> select * from members;
+-----------+--------+
| member_id | name   |
+-----------+--------+
|         1 | John   |
|         2 | Jane   |
|         3 | Mary   |
|         4 | David  |
|         5 | Amelia |
+-----------+--------+

Committees table

CREATE TABLE committees (
   committee_id INT AUTO_INCREMENT,
   name VARCHAR(100),
   PRIMARY KEY (committee_id)
);
INSERT INTO committees(name)
VALUES(''John''),(''Mary''),(''Amelia''),(''Joe'');

Data in Committees table −

mysql> select * from committees;
+--------------+--------+
| committee_id | name   |
+--------------+--------+
|            1 | John   |
|            2 | Mary   |
|            3 | Amelia |
|            4 | Joe    |
+--------------+--------+

Some members are committee members, and some are not. On the other hand, some committee members are in the members table, some are not.

MySQL INNER JOIN

The syntax of using the inner join clause in the SELECT statement is as follows −

SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;

The inner join clause compares each row from the first table with every row from the second table. If values from both rows satisfy the join condition, a new row created in the resuleset whose column contains all columns of the two rows from both tables and includes this new row in the result set. In other words, the inner join clause includes only matching rows from both tables.

If the join condition uses the equality operator (=) with the matching column name being same in both the tables, you can use the USING clause instead of ON clause.

SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);

Example

In the following Node.js code, we shall use the members and Committees tables and obtain their Inner Join.

var mysql = require(''mysql'');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});

var qry =`
SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
INNER JOIN committees c ON c.name = m.name;
`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, results) {
      if (err) throw err;
      results.forEach((row) => {
         console.log(JSON.stringify(row));
      });
   });

   con.end();
});

Output

{"member_id":1,"member":"John","committee_id":1,"committee":"John"}
{"member_id":3,"member":"Mary","committee_id":2,"committee":"Mary"}
{"member_id":5,"member":"Amelia","committee_id":3,"committee":"Amelia"}

MySQL LEFT JOIN

Left join is similar to an inner join. When joining two tables using a left join, the left join selects data starting from the left table. For each row in the left table, the left join compares with every row in the right table. If the values in the two rows satisfy the join condition, the left join clause creates a new row whose columns contain all columns of the rows in both tables and includes this row in the result set.

If the values in the two rows are not matched, the left join clause still creates a new row whose columns contain columns of the row in the left table and NULL for columns of the row in the right table.

Hence, the left join selects all data from the left table whether there are matching rows exist in the right table or not.

In case there are no matching rows from the right table found, the left join uses NULLs for columns of the row from the right table in the result set.

The syntax of a left join clause is as follows −

SELECT column_list 
FROM table_1 
LEFT JOIN table_2 USING (column_name);

Let us change the query string in out Node.js code to the following statement −

Example

var qry =`
SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
LEFT JOIN committees c USING(name);
`;

Output

{"member_id":1,"member":"John","committee_id":1,"committee":"John"}
{"member_id":2,"member":"Jane","committee_id":null,"committee":null}
{"member_id":3,"member":"Mary","committee_id":2,"committee":"Mary"}
{"member_id":4,"member":"David","committee_id":null,"committee":null}
{"member_id":5,"member":"Amelia","committee_id":3,"committee":"Amelia"}

The unmatched columns are filled with NULL data.

To find members who are not the committee members, you add a WHERE clause and IS NULL operator.

SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
LEFT JOIN committees c USING(name)
WHERE c.committee_id IS NULL;

MySQL RIGHT JOIN

The right join clause is similar to the left join clause except that the treatment of left and right tables is reversed. The right join starts selecting data from the right table instead of the left table.

The right join clause selects all rows from the right table and matches rows in the left table. If a row from the right table does not have matching rows from the left table, the column of the left table will have NULL in the final result set.

SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name);

Let us modify the query string variable as follows −

var qry =`
SELECT 
   m.member_id, 
   m.name AS member, 
   c.committee_id, 
   c.name AS committee
FROM
   members m
RIGHT JOIN committees c on c.name = m.name;
`;

The result returns the RIGHT JOIN of two tables −

{"member_id":1,"member":"John","committee_id":1,"committee":"John"}
{"member_id":3,"member":"Mary","committee_id":2,"committee":"Mary"}
{"member_id":5,"member":"Amelia","committee_id":3,"committee":"Amelia"}
{"member_id":null,"member":null,"committee_id":4,"committee":"Joe"}