SQL_join_examples, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'
SELECT @@version AS 'sql server version'

CREATE TABLE pupils
  (
     id   INTEGER PRIMARY KEY,
     NAME VARCHAR(max),
     club VARCHAR(max)
  );

/* Create few records in this table */
INSERT INTO pupils
VALUES     (1,
            'Pupil 1',
            'Club 1');

INSERT INTO pupils
VALUES     (2,
            'Pupil 2',
            'Club 2');

INSERT INTO pupils
VALUES     (3,
            'Pupil 3',
            'Club 2');

INSERT INTO pupils
VALUES     (4,
            'Pupil 4',
            NULL);

CREATE TABLE clubs
  (
     id          INTEGER PRIMARY KEY,
     club_name   VARCHAR(max),
     club_detail VARCHAR(max)
  );

/* Create few records in this table */
INSERT INTO clubs
VALUES     (1,
            'Club 1',
            'Detail 1');

INSERT INTO clubs
VALUES     (2,
            'Club 2',
            'Detail 2');

INSERT INTO clubs
VALUES     (3,
            'Club 3',
            'Detail 3');

INSERT INTO clubs
VALUES     (4,
            'Club 4',
            'Detail 4');

/* Display all the records from the table */
SELECT *
FROM   pupils;

/* Display all the records from the table */
SELECT *
FROM   clubs;

SELECT *
FROM   pupils
       CROSS JOIN clubs;

/*Notice that we get nothing about the pupil with no club or the clubs with
no pupil.
*/
SELECT pupils.NAME,
       clubs.club_name
FROM   pupils
       INNER JOIN clubs
               ON pupils.club = clubs.club_name;

/*Lets try a left outer join. You'll see we now have returned the pupil with no
club.*/
SELECT pupils.NAME,
       clubs.club_name
FROM   pupils
       LEFT OUTER JOIN clubs
                    ON pupils.club = clubs.club_name;

/* Let's also try the right outer join. We should get the club with no pupil 
(but not the pupil with no club).*/
SELECT pupils.NAME,
       clubs.club_name
FROM   pupils
       RIGHT OUTER JOIN clubs
                     ON pupils.club = clubs.club_name;

/* And finally the full outer join */
SELECT pupils.NAME,
       clubs.club_name
FROM   pupils
       FULL OUTER JOIN clubs
                    ON pupils.club = clubs.club_name;

View schema
Execution time: 0,05 sec, rows selected: 43, rows affected: 8, absolute service time: 0,22 sec