A Database usually contains a number of tables. These tables store related data of an application. The data can be fixed data like customers’ basic information. There are transaction tables that store data generated out of user activities like order placing, complaints, financial transactions etc. Multiple tables represent different aspects of an application in terms of its data needs. MySQL Join is the mechanism to fetch data from multiple tables for different data presentation requirements.
It is done by combining different tables in database(s) using JOIN Keyword. My SQL Join defines the syntax with required keywords for joining two or more tables and returns the data from these combined tables.
MySQL JOIN in SELECT statement
MySQL Join is a clause added in a select query to fetch records from more than one table or views. The columns and the rows are selected depending upon the Join criteria.
A MySQL statement with JOIN clause can be created with any columns from the joined tables. If the name of the columns is same in the tables then you must differentiate them by qualifying column names with table name or table alias. It is essential to avoid ambiguity of table column names.
MySQL Join Condition
MySQL Join condition is the criteria on which the tables will connect with each other. The condition is given by using the columns whose values we want to set up the filter for getting data from combined table. On satisfaction of the condition the data will be chosen and displayed back.
The join condition can be given either in the WHERE clause or the FROM clause of the query. This condition defined the basis of a MySQL join. When the SQL statement is executed the specified join condition compares the values of column in one table and values of column in the second table. All the rows which satisfy the condition are returned by the query.
Types of MySQL Join
MySQL allows four types of joins. These are
- INNER JOIN- to get data rows that match values in joined tables
- LEFT JOIN: to get data rows from the left table and rows with matched values in right table
- RIGHT JOIN: to get data rows from the right table and rows with matched values in left table
- CROSS JOIN: to get all data rows from both left and right tables