We are going to use the following Employee table to understand Aliases in MySQL with Examples. The AS keyword is used to define the Alias in SQL statement. Also, if the table names or column names are big or not readable. The Aliases are more useful when multiple columns or multiple tables are used in a single query. The purpose of Aliases is to make table or column names more readable. In MySQL, an Alias means a temporary name given to a table or a column. Please read our previous article where we discussed the LIMIT Clause in MySQL with Examples. In this article, I am going to discuss Alias in MySQL with Examples.

Data Structures and Algorithms Tutorialsīack to: MySQL Tutorials for Beginners and Professionals Alias in MySQL with Examples.The LEFT OUTER JOIN query will now produce a different result than the INNER JOIN. Table1 (WeddingTable, TableSeat, TableSeatID, Name) However, adding a record to table1 for which there is no match in table2 INSERT INTO Since all records match, the result is the same as the INNER JOIN. Where there is no match, the Meal column will contain a NULL value.

This returns all records from table1 regardless of whether there is a match in table2. Other considerations are for when there is data in one table that has no match in the other.Īnom's response uses a LEFT OUTER JOIN. The only other possibility I can think of is if the values in the WeddingTable and TableSeat columns of your actual data tables, in fact, do not match.įor example, assuming TableSeat is CHAR type and table1.TableSeat contains ('001', '002', '003', '001') and table2.TableSeat contains ('01', '002', '3', '01'), then this would be a situation where it would limit to one match due to the TableSeat component of the ON predicate. There are 4 records that match between the two tables.įrom the sample provided, I fail to see why you are only obtaining one combination. Once I resolve this ambiguity in my environment, the INNER JOIN returns 4 records for the data listed in your tables. On One.WeddingTable = Two.WeddingTable and One.TableSeat = Two.TableSeat Select One.WeddingTable, One.TableSeat, TableSeatID, Name, Two.Meal Since WeddingTable and TableSeat appear as column names in both tables and depending on your environment, there may be ambiguity. Desired Results:įirst, I would suggest being more explicit with referencing column names. The results needs to show all 4 lines, being all 3 seats at WeddingTable 001 and the one seat at WeddingTable 002.

Table1 has the convenient TableSeatID, but table2 does not have a comparable ID. Take the situation where there is a wedding where table1 is basically a seating chart, and table2 is the meal option that each table/seat has chosen. I only get one of the criteria 1/criteria 2 combinations even when I know for a fact that there are 3 or 4. ON One.WeddingTable = Two.WeddingTable AND One.TableSeat = Two.TableSeat When I use the join statement here (assuming I identify table1 as One prior to this): SELECT WeddingTable, TableSeat, TableSeatID, Name, Two.Meal There can be anywhere from 1 - 5 values of criteria 2 for each criteria 1 on the table. table2 has column 1 (criteria 1) column 2 (criteria 2) column 3.table1 has column 1 (criteria 1) column 2 (criteria 2) column 3.I am willing to bet that this is a really simple answer as I am a noob to SQL.

Mysql join on multiple columns