Tuesday, January 24, 2012

MySQL's Inner Join

Inner Join can be used to link two tables or more provided that they have the same matching primary key and foreign key.

The samples below link two tables, customers and sales on their customer id.

the records from Customer table

the records from Sales table

the resulting table after inner join,
 summing the total for customer record 3 using the Sum function and Group By

The syntax for INNER JOIN: 

SELECT * FROM table1 INNER JOIN table2 ON table1.PrimaryKey = table2.ForeignKey

Alternative Formats:
SELECT * FROM table1 JOIN table2 ON table1.PrimaryKey = table2.ForeignKey

SELECT *  FROM table1,table2 WHERE  table1.PrimaryKey=table2.ForeignKey

The resulting table above is shown after this command was run on MySQL:

SELECT firstname,lastname,sum(salesamount) as salesPerCustomer from customers inner join sales on customers.customerID=sales.customerID group by lastname


SELECT c.firstname,c.lastname,sum(s.salesamount) as salesPerCustomer from customers as c,sales as s where c.customerID=s.customerID group by lastname