join in mysql

join in mysql

join is used to get records from related tables.

Basic Join

SELECT BookTitle, Copyright, AuthID
FROM Books AS b, AuthorBook AS ab
WHERE b.BookID=ab.BookID
ORDER BY BookTitle;

--------------------------------------------------------------------

SELECT BookTitle, Copyright, ab.AuthID
FROM Books AS b, AuthorBook AS ab
WHERE b.BookID=ab.BookID AND Copyright<1980
ORDER BY BookTitle;

------------------------------------------------------------------------------

Creating Inner Joins and Cross Joins

SELECT BookTitle, Copyright, ab.AuthID
FROM Books AS b JOIN AuthorBook AS ab
ON b.BookID=ab.BookID
WHERE Copyright<1980
ORDER BY BookTitle;
-------------------------------------------------------------------------------------------

SELECT BookTitle, Copyright, CONCAT_WS(‘ ‘, AuthFN, AuthMN, AuthLN) AS Author
FROM Books AS b CROSS JOIN AuthorBook AS ab ON b.BookID=ab.BookID
CROSS JOIN Authors AS a ON ab.AuthID=a.AuthID
WHERE Copyright<1980
ORDER BY BookTitle;

--------------------------------------------------------------------------------------------------------------------

SELECT BookTitle, Copyright, AuthID
FROM Books AS b LEFT JOIN AuthorBook AS ab
ON b.BookID=ab.BookID
ORDER BY BookTitle;