------Vitalie Bancu @vitallybankou    

Listing 7.3b The same join, using WHERE syntax. See

Figure 7.3 for the result.

*/

SELECT au_fname, au_lname, a.city

  FROM authors a, publishers p

  WHERE a.city = p.city;

 

 

SELECT au_fname, au_lname, city

FROM authors

INNER JOIN publishers

USING (city);

 

  ------Vitalie Bancu @vitallybankou

To create a cross join:

 Type:

SELECT columns

FROM table1

CROSS JOIN table2

*/

  ------Vitalie Bancu @vitallybankou

Listing 7.4 A cross join displays all possible

combinations of rows from two tables. See

Figure 7.4 for the result.

*/

SELECT

    au_id,

    pub_id,

    a.state AS "au_state",

    p.state AS "pub_state"

  FROM authors a

  CROSS JOIN publishers p;

  

  ------Vitalie Bancu @vitallybankou

Using WHERE syntax, Listing 7.4 is equivalent

to:

*/

 

SELECT au_id, pub_id,

a.state AS "au_state",

p.state AS "pub_state"

FROM authors a, publishers p;

 

 

SELECT *

FROM authors

CROSS JOIN publishers;

 

SELECT *

FROM authors, publishers;

 

SELECT authors.*, p.pub_id

FROM authors

CROSS JOIN publishers p;

 

SELECT authors.*, p.pub_id

FROM authors, publishers p;

 

SELECT authors.*, publishers.*,titles.*

FROM authors CROSS JOIN  publishers CROSS JOIN TITLES; 

 

 

 

SELECT au_fname, au_lname, a.city

  FROM authors a, publishers p

  WHERE   1=1

  --a.city = p.city;

  

  

  SELECT au_fname, au_lname, a.city

  FROM authors a

  CROSS JOIN publishers p

    --ON a.city = p.city;

    

    

  SELECT au_fname, au_lname, a.city

  FROM authors a

  INNER JOIN publishers p

    ON 1=1;

  --  a.city = p.city;

    

    SELECT au_fname, au_lname, a.city

    FROM authors a

    LEFT JOIN publishers p

    ON 1=1;

    

    SELECT au_fname, au_lname, a.city

    FROM authors a

    RIGHT JOIN publishers p

    ON 1=1;

    

    SELECT au_fname, au_lname, a.city

    FROM authors a

    FULL JOIN publishers p

    ON 1=1;

    

      ------Vitalie Bancu @vitallybankou

    To create a natural join:

 Type:

SELECT columns

FROM table1

NATURAL JOIN table2

    */

    

 

SELECT

    title_id,

    pub_id,

    pub_name

  FROM publishers

  NATURAL JOIN titles;

  

  

  DESCRIBE titles;

  

SELECT

    title_id,

    pub_id,

    pub_name,

    advance

  FROM publishers

  NATURAL JOIN titles

  NATURAL JOIN royalties

  WHERE advance < 20000;

  

  

SELECT t.title_id, t.pub_id,

p.pub_name

FROM publishers p, titles t

WHERE p.pub_id = t.pub_id;

 

SELECT t.title_id, t.pub_id,

p.pub_name, r.advance

FROM publishers p, titles t,

royalties r

WHERE p.pub_id = t.pub_id

AND t.title_id = r.title_id

AND r.advance < 20000;

 

SELECT t.title_id, t.pub_id,

p.pub_name

FROM publishers p

INNER JOIN titles t

ON p.pub_id = t.pub_id;

 

SELECT t.title_id, t.pub_id,

p.pub_name, r.advance

FROM publishers p

INNER JOIN titles t

ON p.pub_id = t.pub_id

INNER JOIN royalties r

ON t.title_id = r.title_id

WHERE r.advance < 20000;