MySQL - Joins - The Diigo Meta page

• 14 Feb 17
  

  karel barel

  mysql sql tutorial joins

• 19 May 14
  

  Hans-Peter Zahno

  mysql join select tutorial

• 25 Apr 13
  

  Niels van Gelderen

  mysql joins join sql tutorial programming database query

• 17 Apr 13
  

  unghorton

  mysql

• 23 Sep 12
  

  Victor Sotto

  Join MySQL

• 17 Feb 12
  

  Andy Cook

  learning

• 21 Jun 11
  

  Sreejitha Roshin

  mysql join database query joins

  • a join   is. Basically it is the combining of two rows based on the comparative values   in selected columns
  • A cross-join between two tables takes the data from each row in table1 and   joins it to the data from each row in table2
  • mysql> SELECT cds.artist, genres.genre   -> FROM cds, genres;
  • In the equi-join the comparison we are making between two columns is that they   match the same value. We can use this method to select certain fields from both   tables and only the correct rows will be joined together.
  • mysql> SELECT cds.artist, cds.title, genres.genre   -> FROM cds, genres   -> WHERE (cds.genreID = genres.genreID);
  • The left join is a mechanism used to join tables before we add other   conditions such as WHERE etc.
  • mysql> SELECT cds.artist, cds.title, genres.genre   -> FROM cds   -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID;
  • An important thing to note with this particular join is that even if there  are no records in the second table (in this case 'genres') data will still  be displayed from the first table.
  • a RIGHT   JOIN which is a variation where all the data on the RIGHT side of the join (the   second table) is returned regardless of the presence of data from the first   table.
  • SELECT cds.artist, cds.title, genres.genreID, genres.genre   -> FROM cds  -> RIGHT JOIN genres   -> ON cds.genreID = genres.genreID;
  • every record of the RIGHT side must be returned at least once   by the RIGHT JOIN.
  • SELECT cds.artist, cds.title, genres.genre   -> FROM cds   -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID   -> WHERE genres.genre = 'pop';

  • use this if the   columns you are carrying out the join on have the same name.

     
  • SELECT <column_name>  FROM <Table1>  LEFT JOIN <Table2>  USING (<column_name>)
  • SELECT cds.artist, cds.title, genres.genre   -> FROM cds   -> LEFT JOIN genres   -> USING (genreID);
  • We have a cds table that contains the foreign keys (i.e. values that correspond   to primary keys in another table) called cds.genreID and cds.artistID which   also exist in the genres and artists tables. A three table join can be achieved   using another version of the Equi-Join or Inner Join where   we can use the WHERE clause to limit the returned records based on comparing   the artistID and the genreID.
  • the first JOIN creates a virtual   table (from joining tables one and two) which can then be joined to the third   table.
  • SELECT artists.Artist, cds.title, genres.genre   -> FROM cds  -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID   -> LEFT JOIN artists   -> ON cds.artistID = artists.artistID   -> WHERE (genres.genre = 'Pop')
  • SELECT artists.Artist, cds.title, label.Label, genres.genre   -> FROM cds  -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID   -> LEFT JOIN artists   -> ON cds.artistID = artists.artistID  -> LEFT JOIN label   -> ON cds.labelID = label.labelID;
  • This method of adding tables and performing JOINS will work only if one TABLE  has all the foreign keys.
  • UPDATE cds->LEFT JOIN artists   -> ON cds.artistID = artists.artistID  -> SET   -> cds.title = 'A Funk Odyssey',   -> artists.name = 'Jamiroquai'  -> WHERE (cds.cdID = '2');
  • DELETE cds, artist   -> FROM cds, artist  -> WHERE (cds.artistID = artists.artistID)   -> AND (cds.artistID = '3');

  20 more annotations...

• 14 Jun 11
  

  julialt

  very helpful

  databases programming reference sql tutorial

  • left join is a mechanism used to join tables before we add other   conditions such as WHERE etc.

  • Syntax:

     

    SELECT <column_name>  FROM <Table1>  LEFT JOIN <Table2>  ON Table1.column = Table2.column
  • important thing to note with this particular join is that even if there  are no records in the second table (in this case 'genres') data will still  be displayed from the first table
  • data from the LEFT of the  join will be displayed
  • mysql> SELECT cds.artist, cds.title, genres.genre   -> FROM cds   -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID   -> WHERE genres.genre = 'pop';
  • can use this if the   columns you are carrying out the join on have the same name.
  • 'USING' clause
  • joining the tables where cds.genreID is the same   as genres.genreID
  • genreID is the name of a column in BOTH   of tables we are using for the join
  • mysql> SELECT cds.artist, cds.title, genres.genre   -> FROM cds   -> LEFT JOIN genres   -> USING (genreID);
  • possible to join more than two tables
  • Joining Three Tables
  • have a cds table that contains the foreign keys (i.e. values that correspond   to primary keys in another table) called cds.genreID and cds.artistID which   also exist in the genres and artists tables
  • use the WHERE clause to limit the returned records based on comparing   the artistID and the genreID.
  • problem
  • already have a fairly complex   WHERE clause
  • Ideally what we want is   to have a LEFT / RIGHT JOIN
  • solution to this is to use a series of joins
  • result is passed to a second join
  • mysql> SELECT artists.Artist, cds.title, genres.genre   -> FROM cds  -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID   -> LEFT JOIN artists   -> ON cds.artistID = artists.artistID   -> WHERE (genres.genre = 'Pop');
  • 'label' table
  • could also join a fourth table
  • statement   to join all four tables and display the results.
  • SELECT artists.Artist, cds.title, label.Label, genres.genre   -> FROM cds  -> LEFT JOIN genres   -> ON cds.genreID = genres.genreID   -> LEFT JOIN artists   -> ON cds.artistID = artists.artistID  -> LEFT JOIN label   -> ON cds.labelID = label.labelID;

  22 more annotations...

• 04 May 11
  

  Clarence S

  mysql join

• 05 Apr 11
  

  leo alvarez

  mysql join query

• 06 Feb 11
  

  lihao sun

  column

• 19 Nov 10
  

  sainathrapaka

  mysql tutorial programming database development webdesign reference join sql joins

• 18 Nov 10
  

  Quang Van

  mysql join select advance advanced query fromdelicious

• 29 Sep 10
  

  carlos_ferreira

  MySQL Joins tutorial

• 09 Sep 10
  

  Ramesh Babu

  mysql joins sql

• 04 Jun 10
  

  Oscar Bout

  tutorial sql

• 29 Apr 10
  

  Roberto Calderon
• 19 Apr 10
  

  Jan Gorski

  Using MySQL, Joins
  
  Workshop Requirements

  mysql joins sql

• 09 Mar 10
  

  Francois Tcha

  Great Joins examples

  mysql joins

• 08 Feb 10
  

  Eduardo Matos

  mysql join database

• 21 Dec 09
  

  John Rubbles

  mysql

• 09 Dec 09
  

  vivek saini
  • This is why we have been   prefixing the column name with the table name to ensure we can tell the difference
  • This 'super-row' exists only for the duration of the query   that creates it
  • A variation on the Left Join is the 'USING' clause.
  • rather than being used to evaluate and display   the data from it, the result is passed to a second join
  • A good way to think of this is that the first JOIN creates a virtual   table (from joining tables one and two) which can then be joined to the third   table.

  3 more annotations...

• 16 Oct 09
  

  Michael Gurnett

  SELECT cds.artist, cds.title, genres.genre
  -> FROM cds
  -> LEFT JOIN genres
  -> ON cds.genreID = genres.genreID;

  mysql

• 

  jedi hawk

  yet another sql tutorial

  mysql joins sql docs documentation development

• 06 Oct 09
  

  Work MySQL

• 03 Jun 09
  

  J Muller

  mysql tutorial reference joins

• 18 May 09
  

  varkas

  his is because every record of the RIGHT side must be returned at least once by the RIGHT JOIN.

  sql

• 14 Apr 09
  

  Rob Campbell

  tutorial mysql joins join

• 21 Jan 09
  

  Dave Ware

  A very good tutorial on SQL join/left joins.

  mysql

• 16 Jan 09
  

  Steve Ferwerda

  mysql queries join

• 08 Oct 08
  

  cornfelt

  mysql joins tutorial

• 27 May 08
  

  Ian McGregor

  SELECT <column_name> FROM <Table1>, <Table2> WHERE (Table1.column = Table2.column)

  mysql

• 09 May 08
  

  slith one

  mysql sql

• 08 May 08
  

  Marc Slayton

  mysql join sql self-join delicious

• 02 Apr 08
  

  Manimaran vadivel

  Mysql

  • | westlife | westlife | NULL
• 04 Mar 08
  

  Sanjay Mysoremutt

  database delicious

• 04 Oct 07
  

  Andre Marcanth

  sql database php mysql

• 12 Jul 07
  

  papayasoft

  good examples of joins with insert, update, delete. a nice example of refactoring a a field into an external table.

  MySQL joins SQL join insert update delete complex

• 13 Apr 07
  

  axelseis

  mysql tutorials programming

• 17 Mar 07
  

  mysql tutorial joins programming sql development webdesign database delicious

• 14 Mar 07
  

  Kevin Basarab

  mysql programming reference

• 22 Aug 06
  

  Brian Drummond

  mysql tutorial

• 08 Aug 06
  

  Kim Woodbridge

  mysql programming joins select

Would you like to comment?

Join Diigo for a free account, or sign in if you are already a member.