mysql
  1. mysql-join
PreviousNext

JOIN - (MySQL Join)

JOIN is a keyword used in SQL to combine rows from two or more tables based on a related column between them. The JOIN operation is one of the fundamental concepts in relational databases. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of JOIN in MySQL.

Syntax

The basic syntax for joining two tables together in MySQL is:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

There are four basic types of JOIN operations in MySQL:

  • INNER JOIN (or JOIN)
  • LEFT JOIN (or LEFT OUTER JOIN)
  • RIGHT JOIN (or RIGHT OUTER JOIN)
  • FULL JOIN (or FULL OUTER JOIN)

Example

Suppose we have two tables, "users" and "orders", and we want to join them based on the "user_id" column. The "users" table has the following columns:

  • id
  • name
  • email

The "orders" table has the following columns:

  • order_id
  • user_id
  • order_amount

To get the name and email of every user who has made an order, we can use the following query:

SELECT users.name, users.email, orders.order_amount
FROM users
JOIN orders
ON users.id = orders.user_id;

Output

Here's what the output of the above query might look like:

+------+-----------------------+--------------+
| name | email                 | order_amount |
+------+-----------------------+--------------+
| John | john@example.com      |         10.5 |
| Jane | jane@example.com      |            5 |
| Bob  | bob@example.com       |           20 |
+------+-----------------------+--------------+

Explanation

In the example above, we joined the "users" and "orders" tables based on the "user_id" column (which is "users.id" and "orders.user_id", respectively). This means that we only get data for users who have made orders (since we're using an INNER JOIN).

We used the SELECT keyword to specify the columns we want to retrieve from the joined table. In this case, we're getting the "name" and "email" columns from the "users" table, as well as the "order_amount" column from the "orders" table.

Use

JOIN operations are useful for combining data from two or more tables in a relational database. They allow you to retrieve data that is spread across multiple tables, based on a related column between them.

JOIN operations can also be nested and combined with other SQL clauses, such as WHERE, GROUP BY, and ORDER BY, to give you more control over the data you retrieve.

Important Points

  • There are four basic types of JOIN operations in MySQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
  • JOIN operations are based on a related column between two or more tables.
  • JOIN operations can be nested and combined with other SQL clauses to provide more control over the data you retrieve.

Summary

In this tutorial, we discussed JOIN, which is a keyword used in SQL to combine rows from two or more tables based on a related column between them. We covered the syntax, example, output, explanation, use, and important points of JOIN in MySQL. With this knowledge, you can now use JOIN in your MySQL queries to retrieve data from multiple tables in a relational database.

BETWEENInner Join
Published on: