Assuming you're joining on columns with no duplicates, which is a very common case:
An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.
Examples
Suppose you have two tables, with a single column each, and data as follows:
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Right outer join
A right outer join will give all rows in B, plus any common rows in A.
select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.* from a,b where a.a(+) = b.b;
a | b
-----+----
3 | 3
4 | 4
null | 5
null | 6
Full outer join
A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
Simple Example: Let's say you have a Students
table, and a Lockers
table. In SQL, the first table you specify in a join, Students
, is the LEFT table, and the second one, Lockers
, is the RIGHT table.
Each student can be assigned to a locker, so there is a LockerNumber
column in the Student
table. More than one student could potentially be in a single locker, but especially at the beginning of the school year, you may have some incoming students without lockers and some lockers that have no students assigned.
For the sake of this example, let's say you have 100 students, 70 of which have lockers. You have a total of 50 lockers, 40 of which have at least 1 student and 10 lockers have no student.
INNER JOIN
is equivalent to "show me all students with lockers".
Any students without lockers, or any lockers without students are missing.
Returns 70 rows
LEFT OUTER JOIN
would be "show me all students, with their corresponding locker if they have one".
This might be a general student list, or could be used to identify students with no locker.
Returns 100 rows
RIGHT OUTER JOIN
would be "show me all lockers, and the students assigned to them if there are any".
This could be used to identify lockers that have no students assigned, or lockers that have too many students.
Returns 80 rows (list of 70 students in the 40 lockers, plus the 10 lockers with no student)
FULL OUTER JOIN
would be silly and probably not much use.
Something like "show me all students and all lockers, and match them up where you can"
Returns 110 rows (all 100 students, including those without lockers. Plus the 10 lockers with no student)
CROSS JOIN
is also fairly silly in this scenario.
It doesn't use the linked lockernumber
field in the students table, so you basically end up with a big giant list of every possible student-to-locker pairing, whether or not it actually exists.
Returns 5000 rows (100 students x 50 lockers). Could be useful (with filtering) as a starting point to match up the new students with the empty lockers.
Best Answer
Wait-Die scheme
It is a non-preemptive technique for deadlock prevention. When transaction Tn requests a data item currently held by Tk, Tn is allowed to wait only if it has a timestamp smaller than that of Tk (That is Tn is older than Tk), otherwise Tn is killed ("die").
In this scheme, if a transaction requests to lock a resource (data item), which is already held with a conflicting lock by another transaction, then one of the two possibilities may occur:
Timestamp(Tn) < Timestamp(Tk) − that is Tn, which is requesting a conflicting lock, is older than Tk − then Tn is allowed to "wait" until the data-item is available.
Timestamp(Tn) > Timestamp(Tk) − that is Tn is younger than Tk − then Tn is killed ("dies"). Tn is restarted later with a random delay but with the same timestamp(n).
This scheme allows the older transaction to "wait" but kills the younger one ("die").
Example
Suppose that transaction T5, T10, T15 have time-stamps 5, 10 and 15 respectively.
If T5 requests a data item held by T10 then T5 will "wait".
If T15 requests a data item held by T10, then T15 will be killed ("die").
Wound-Wait scheme
It is a preemptive technique for deadlock prevention. It is a counterpart to the wait-die scheme. When Transaction Tn requests a data item currently held by Tk, Tn is allowed to wait only if it has a timestamp larger than that of Tk, otherwise Tk is killed (i.e. Tk is wounded by Tn).
In this scheme, if a transaction requests to lock a resource (data item), which is already held with conflicting lock by some another transaction, one of the two possibilities may occur:
Timestamp(Tn) < Timestamp(Tk), then Tn forces Tk to be killed − that is Tn "wounds" Tk. Tk is restarted later with a random delay but with the same timestamp(k).
Timestamp(Tn) > Timestamp(Tk), then Tn is forced to "wait" until the resource is available.
This scheme allows the younger transaction requesting a lock to "wait" if the older transaction already holds a lock, but forces the younger one to be suspended ("wound") if the older transaction requests a lock on an item already held by the younger one.
Example
Again, suppose that Transactions T5, T10, T15 have time-stamps 5, 10 and 15 respectively.
If T5 requests a data item held by T10, then data item will be preempted from T10 and T10 will be suspended. ("wounded")
If T15 requests a data item held by T10, then T15 will "wait".
Summary
In both the cases, only the transaction that enters the system at a later timestamp (i.e. the younger transaction) might be killed and restarted.