1SELECT
2 m.member_id,
3 m.name member,
4 c.committee_id,
5 c.name committee
6FROM
7 members m
8INNER JOIN committees c
9 ON c.name = m.name;
1-- MySQL INNER JOINS return all rows from multiple tables where the join condition is met.
2
3SELECT columns
4FROM table1
5INNER JOIN table2
6ON table1.column = table2.column;
7
8-- LEFT OUTER JOIN
9-- Another type of join is called a MySQL LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
10
11SELECT columns
12FROM table1
13LEFT JOIN table2
14ON table1.column = table2.column;
15
16-- RIGHT OUTER JOIN
17-- Another type of join is called a MySQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
18
19SELECT columns
20FROM table1
21RIGHT JOIN table2
22ON table1.column = table2.column;
23
24-- The mySQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.
25
26-- If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
27
28SELECT columns
29FROM table1
30CROSS JOIN table2;
1A relational database consists of multiple related tables linking together using common columns which are known as foreign key columns. Because of this, data in each table is incomplete from the business perspective.
2MySQL supports the following types of joins:
3
4Inner join
5Left join
6Right join
7Cross join
8
9The following shows the basic syntax of the inner join clause that joins two tables table_1 and table_2:
10
11SELECT column_list
12FROM table_1
13INNER JOIN table_2 ON join_condition;
14
15
16SELECT column_list
17FROM table_1
18INNER JOIN table_2 USING (column_name);
19
20SELECT column_list
21FROM table_1
22LEFT JOIN table_2 USING (column_name);
23Here is the syntax of the right join:
24
25SELECT column_list
26FROM table_1
27RIGHT JOIN table_2 ON join_condition;
28
29The following shows the basic syntax of the cross join clause:
30
31SELECT select_list
32FROM table_1
33CROSS JOIN table_2;
1Joins are used with select statement. it is used to select data from multiple table.
2Types:
3MySQL INNER JOIN (or simple join)
4MySQL LEFT OUTER JOIN (or LEFT JOIN)
5MySQL RIGHT OUTER JOIN (or RIGHT JOIN)
6
7Inner JOIN :
8The INNER JOIN is used to return all rows from multiple tables where the join condition is satisfied. It is the most common type of join.
9
10Left Outer Join:
11The LEFT OUTER JOIN returns all rows from the left hand table specified in the ON condition and only those rows from the other table where the join condition is fulfilled.
12
13Right Outer Join:
14The Right Outer Join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where he join condition is fulfilled.