{"id":249,"date":"2019-10-06T15:52:19","date_gmt":"2019-10-06T08:52:19","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=249"},"modified":"2020-04-11T23:24:10","modified_gmt":"2020-04-11T16:24:10","slug":"mariadb-join","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-join\/","title":{"rendered":"MariaDB Join"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MariaDB join<\/code> clause to select data from multiple tables.<\/p>\n\n\n\n

So far, you have learned how to retrieve data from a single table using a simple select<\/a><\/code> statement.<\/p>\n\n\n\n

To select data from multiple related tables, you use the select<\/code> statement with join<\/code> clauses. The join<\/code> clauses associate the rows in one table with the rows in another table based on a specified condition.<\/p>\n\n\n\n

MariaDB supports various kinds of joins such as inner join<\/a><\/code>, left join<\/a><\/code>, right join<\/code>, and cross join<\/code>.<\/p>\n\n\n\n

Note that MariaDB doesn’t support the full outer join<\/code> or full join<\/code>.<\/p>\n\n\n\n

To demonstrate join clauses, we will use the guests<\/code> and vips<\/code> tables from the sample database<\/a>:<\/p>\n\n\n\n

The guests<\/code> table:<\/p>\n\n\n

select<\/span> * from<\/span> guests;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
The vips<\/code> table:<\/p>\n\n\n
select<\/span> * from<\/span> vips;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
MariaDB inner join clause<\/h2>\n\n\n\n
Suppose that you want to join two tables t1<\/code> and t2<\/code>. The following shows the syntax of the inner join<\/a><\/code> clause that joins the two tables:<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\ninner<\/span> join<\/span> t2 \n    on<\/span> join_condition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The inner join<\/code> compares each row from the table t1<\/code> with every row from the table t2<\/code>.<\/p>\n\n\n\n
If values of both rows cause the join condition to evaluate to true, the inner join<\/code> creates a new row with columns that contain columns, from both rows, specified by the select list, and includes this new row in the result set.<\/p>\n\n\n\n
Typically, you match rows from the table t1<\/code> with rows from the table t2<\/code> using the equal operator (=):<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\ninner<\/span> join<\/span> t2\n    on<\/span> t2.column1 = t2.column2;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If columns used for matching have the same names, you can use the using<\/code> clause syntax:<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\ninner<\/span> join<\/span> t2 using<\/span> (column<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the inner join<\/code> to find people that appear on both tables guests<\/code> and vips<\/code>:<\/p>\n\n\n
select<\/span> \n    g.guest_id, \n    g.name, \n    v.vip_id, \n    v.name\nfrom<\/span> guests g\ninner<\/span> join<\/span> vips v \n    on<\/span> v.name = g.name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
Since the column for matching rows in both tables vips<\/code> and guests<\/code> have the same name, you can use the using<\/code> clause syntax as follows:<\/p>\n\n\n
select<\/span> \n    g.guest_id, \n    g.name, \n    v.vip_id, \n    v.name\nfrom<\/span> guests g\ninner<\/span> join<\/span> vips v using<\/span> (name<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture illustrates the inner join operation:<\/p>\n\n\n\n
\"MariaDB<\/figure>\n\n\n\n
MariaDB left join clause<\/h2>\n\n\n\n
The left join<\/a><\/code> introduces the concepts of left and right tables.<\/p>\n\n\n\n
Support that t1<\/code> and t2<\/code> are the left and right tables respectively.<\/p>\n\n\n\n
The following illustrates the syntax of the left join:<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\nleft<\/span> join<\/span> t2 \n    on<\/span> join_condition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Similar to the inner join<\/code>, if you use the equal operator (=) to match rows and the names of columns used for matching are the same, you can use the using<\/code> clause syntax:<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\nleft<\/span> join<\/span> t2 \n    using<\/span> (column_name);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The left join<\/code> retrieves data starting from the left table. It compares each row from the left table (t1<\/code>) with every row from the right table (t2<\/code>). If both rows cause the join condition to evaluate to true, the left join<\/code> creates a new row whose columns include columns of both rows specified by the select list.<\/p>\n\n\n\n
If left join<\/code> doesn’t find any matching row in the right table, it still creates a new row whose columns include columns of the row from the left table and null<\/code> values for columns of the row from the right table.<\/p>\n\n\n\n
Simply put, the left join retrieves all rows from the left table (t1<\/code>) whether they have matching rows from the right table or not.<\/p>\n\n\n\n
The following example uses the left join<\/code> to join the guests<\/code> table with the vips<\/code> table:<\/p>\n\n\n
select<\/span> \n    g.guest_id, \n    g.name, \n    v.vip_id, \n    v.name\nfrom<\/span> guests g\nleft<\/span> join<\/span> vips v \n    on<\/span> v.name = g.name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
The following query uses the using<\/code> syntax:<\/p>\n\n\n
select<\/span> \n    g.guest_id, \n    g.name, \n    v.vip_id, \n    v.name\nfrom<\/span> guests g\nleft<\/span> join<\/span> vips v\n    using<\/span>(name<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture illustrates the left join:<\/p>\n\n\n\n
\"MariaDB<\/figure>\n\n\n\n
MariaDB right join clause<\/h2>\n\n\n\n
The right join<\/code> is just a reversed version of the left join<\/code>:<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\nright<\/span> join<\/span> t2 on<\/span> join_condition;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement uses the right join<\/code> clause to join the guests<\/code> with the vips<\/code> tables:<\/p>\n\n\n
select<\/span> \n    g.guest_id, \n    g.name, \n    v.vip_id, \n    v.name\nfrom<\/span> guests g\nright<\/span> join<\/span> vips v \n    on<\/span> v.name = g.name;\t\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MariaDB<\/figure>\n\n\n\n
The following picture illustrates the right join<\/code> operation:<\/p>\n\n\n\n
\"MariaDB<\/figure>\n\n\n\n
MariaDB cross join clause<\/h2>\n\n\n\n
The cross join<\/code> makes a Cartesian product of rows from the joined tables.<\/p>\n\n\n\n
Unlike the inner join<\/code>, left join<\/code>, and right join<\/code>, the cross join<\/code> doesn’t have a join predicate:<\/p>\n\n\n
select<\/span> select_list\nfrom<\/span> t1\ncross<\/span> join<\/span> t2;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the cross join combines each row from t1<\/code> table with every row from the t2<\/code> table to form the result set.<\/p>\n\n\n\n
If t1<\/code> has n<\/code> rows, t2<\/code> has m<\/code> rows, the cross join<\/code> of t1<\/code> and t2<\/code> will result in nxm<\/code> rows.<\/p>\n\n\n\n
The following example uses the cross join<\/code> to join the guests<\/code> with vips<\/code> table:<\/p>\n\n\n
select<\/span> \n    g.guest_id, \n    g.name, \n    v.vip_id, \n    v.name\nfrom<\/span> guests g\ncross<\/span> join<\/span> vips v;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns 25 rows (5×5):<\/p>\n\n\n\n
In this tutorial, you have learned how to use MariaDB join clause to select data from multiple tables.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t