What basic example I can give for inner join and outer join in SQL?

Question

Please help me to understand and best example for my question.

I can not understand how to create the object and use them.

thank you

Answer 1

Her some super simple stuff:

Prepare DB:

CREATE TABLE `table1` (
  `id` int(11) NOT NULL DEFAULT 0,
  `value` text not null DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `table2` (
  `id` int(11) NOT NULL DEFAULT 0,
  `value` text not null DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO table1 VALUES
(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five');

INSERT INTO table2 VALUES
(3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven');

Execute queries:

SELECT t1.*, t2.* FROM table1 t1 JOIN table2 t2 on t1.id = t2.id;
SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 on t1.id = t2.id;
+----+-------+----+-------+
| id | value | id | value |
+----+-------+----+-------+
|  3 | three |  3 | three |
|  4 | four  |  4 | four  |
|  5 | five  |  5 | five  |
+----+-------+----+-------+
SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN table2 t2 on t1.id = t2.id;
SELECT t1.*, t2.* FROM table1 t1 LEFT OUTER JOIN table2 t2 on t1.id = t2.id;
+----+-------+------+-------+
| id | value | id   | value |
+----+-------+------+-------+
|  1 | one   | NULL | NULL  |
|  2 | two   | NULL | NULL  |
|  3 | three |    3 | three |
|  4 | four  |    4 | four  |
|  5 | five  |    5 | five  |
+----+-------+------+-------+

Answer 2

Let's say you have 2 tables:

ID  Name
1   Smith
2   Novak
3   Tarantino

and the second table

ID  Cash
2   300
3   490
4   500

INNER JOIN will join records that have match in both tables, i.e. in the result table only ID 2 and 3 will be included, because they have match. Result would look like:

ID  Name       Cash
2   Novak      300
3   Tarantino  490

OUTER JOIN on the other hand, will match everything, irrespectively whether they have match or not. If record doesn't have match, then value corresponiding to certain row will be NULL, i.e. Smith will have NULL in Cash column and record corresponding to 500 in Cash column will have NULL in Name column. Result would look like:

ID  Name       Cash
1   Smith      NULL
2   Novak      300
3   Tarantino  490
4   NULL       500