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
Development of Lotus Notes began over 20 years ago, with version 1 released in 1989. It was developed by Ray Ozzie, currently Chief Software Architect for Microsoft.
Lotus Notes (the client) and Domino (the server) have been around for a long time and are mature well featured products. It has:
Backwards compatibility has always been a strong feature of Notes Domino and it is not uncommon to find databases that were developed for version 3 running flawlessly in the most up to date versions. IBM puts a huge amount of effort into this and it has a large bearing on how the product currently operates.
-
CouchDB was created by Damien Katz, starting development in 2004. He had previously worked for IBM on Notes Domino, developing templates and eventually completely rewriting one of the core features, the formula engine, for ND6.
CouchDB shares a basic concept of a document oriented database with views that Notes Domino has.
In this model "documents" are just arbitrary collections of values that are stored some how. In CouchDB the documents are JSON objects of arbitrary complexity. In Notes the values are simple name value pairs, where the values can be strings, numbers, dates or arrays of those.
Views are indexes of the documents in the database, displaying certain value, calculating others and excluding undesired docs. Once the index is build they are incrementally updated when any document in the database changes (created updated or deleted).
In CouchDB views are build by running a mapping function on each document in the database. The mapping function calls an emit method with a JSON object for every index entry it wants to create for the given document. This JSON object can be arbitrarily complex. CouchDB can then run a second reducing function on the mapped index of the view.
In Notes Domino views are built by running a select function (written in Notes Domino formula language) on each document in the database. The select function simply defines if the document should be in the view or not. Notes Domino view design also defines a number of columns for the view. Each column has a formula that is run against the selected document to determine the value for that column.
CouchDB is able to produce much more sophisticated view indexes than Notes Domino can.
CouchDB also has a replication system.
-
Summary ( TL;DR ) : CouchDB is brand new software that is developing a core that has a similar conceptual but far more sophisticated design to that used in Lotus Notes Domino. Lotus Notes Domino is a mature fully featured product that is capable of being deployed today. CouchDB is starting from scratch, building a solid foundation for future feature development. Lotus Notes Domino is continuing to develop new features, but is doing so on a 20 year old platform that strives to maintain backwards compatibility. There are features in Notes Domino that you might wish were in CouchDB, but there are also features in Notes Domino that are anachronistic in today's world.