R – Should I use a dedicated network channel between the database and the application server

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

Summary

A TCP socket is an endpoint instance defined by an IP address and a port in the context of either a particular TCP connection or the listening state.

A port is a virtualisation identifier defining a service endpoint (as distinct from a service instance endpoint aka session identifier).

A TCP socket is not a connection, it is the endpoint of a specific connection.

There can be concurrent connections to a service endpoint, because a connection is identified by both its local and remote endpoints, allowing traffic to be routed to a specific service instance.

There can only be one listener socket for a given address/port combination.

Exposition

This was an interesting question that forced me to re-examine a number of things I thought I knew inside out. You'd think a name like "socket" would be self-explanatory: it was obviously chosen to evoke imagery of the endpoint into which you plug a network cable, there being strong functional parallels. Nevertheless, in network parlance the word "socket" carries so much baggage that a careful re-examination is necessary.

In the broadest possible sense, a port is a point of ingress or egress. Although not used in a networking context, the French word porte literally means door or gateway, further emphasising the fact that ports are transportation endpoints whether you ship data or big steel containers.

For the purpose of this discussion I will limit consideration to the context of TCP-IP networks. The OSI model is all very well but has never been completely implemented, much less widely deployed in high-traffic high-stress conditions.

The combination of an IP address and a port is strictly known as an endpoint and is sometimes called a socket. This usage originates with RFC793, the original TCP specification.

A TCP connection is defined by two endpoints aka sockets.

An endpoint (socket) is defined by the combination of a network address and a port identifier. Note that address/port does not completely identify a socket (more on this later).

The purpose of ports is to differentiate multiple endpoints on a given network address. You could say that a port is a virtualised endpoint. This virtualisation makes multiple concurrent connections on a single network interface possible.

It is the socket pair (the 4-tuple consisting of the client IP address, client port number, server IP address, and server port number) that specifies the two endpoints that uniquely identifies each TCP connection in an internet. (TCP-IP Illustrated Volume 1, W. Richard Stevens)

In most C-derived languages, TCP connections are established and manipulated using methods on an instance of a Socket class. Although it is common to operate on a higher level of abstraction, typically an instance of a NetworkStream class, this generally exposes a reference to a socket object. To the coder this socket object appears to represent the connection because the connection is created and manipulated using methods of the socket object.

In C#, to establish a TCP connection (to an existing listener) first you create a TcpClient. If you don't specify an endpoint to the TcpClient constructor it uses defaults - one way or another the local endpoint is defined. Then you invoke the Connect method on the instance you've created. This method requires a parameter describing the other endpoint.

All this is a bit confusing and leads you to believe that a socket is a connection, which is bollocks. I was labouring under this misapprehension until Richard Dorman asked the question.

Having done a lot of reading and thinking, I'm now convinced that it would make a lot more sense to have a class TcpConnection with a constructor that takes two arguments, LocalEndpoint and RemoteEndpoint. You could probably support a single argument RemoteEndpoint when defaults are acceptable for the local endpoint. This is ambiguous on multihomed computers, but the ambiguity can be resolved using the routing table by selecting the interface with the shortest route to the remote endpoint.

Clarity would be enhanced in other respects, too. A socket is not identified by the combination of IP address and port:

[...]TCP demultiplexes incoming segments using all four values that comprise the local and foreign addresses: destination IP address, destination port number, source IP address, and source port number. TCP cannot determine which process gets an incoming segment by looking at the destination port only. Also, the only one of the [various] endpoints at [a given port number] that will receive incoming connection requests is the one in the listen state. (p255, TCP-IP Illustrated Volume 1, W. Richard Stevens)

As you can see, it is not just possible but quite likely for a network service to have numerous sockets with the same address/port, but only one listener socket on a particular address/port combination. Typical library implementations present a socket class, an instance of which is used to create and manage a connection. This is extremely unfortunate, since it causes confusion and has lead to widespread conflation of the two concepts.

Hagrawal doesn't believe me (see comments) so here's a real sample. I connected a web browser to http://dilbert.com and then ran netstat -an -p tcp. The last six lines of the output contain two examples of the fact that address and port are not enough to uniquely identify a socket. There are two distinct connections between 192.168.1.3 (my workstation) and 54.252.94.236:80 (the remote HTTP server)

  TCP    192.168.1.3:63240      54.252.94.236:80       SYN_SENT
  TCP    192.168.1.3:63241      54.252.94.236:80       SYN_SENT
  TCP    192.168.1.3:63242      207.38.110.62:80       SYN_SENT
  TCP    192.168.1.3:63243      207.38.110.62:80       SYN_SENT
  TCP    192.168.1.3:64161      65.54.225.168:443      ESTABLISHED

Since a socket is the endpoint of a connection, there are two sockets with the address/port combination 207.38.110.62:80 and two more with the address/port combination 54.252.94.236:80.

I think Hagrawal's misunderstanding arises from my very careful use of the word "identifies". I mean "completely, unambiguously and uniquely identifies". In the above sample there are two endpoints with the address/port combination 54.252.94.236:80. If all you have is address and port, you don't have enough information to tell these sockets apart. It's not enough information to identify a socket.

Addendum

Paragraph two of section 2.7 of RFC793 says

A connection is fully specified by the pair of sockets at the ends. A local socket may participate in many connections to different foreign sockets.

This definition of socket is not helpful from a programming perspective because it is not the same as a socket object, which is the endpoint of a particular connection. To a programmer, and most of this question's audience are programmers, this is a vital functional difference.

@plugwash makes a salient observation.

The fundamental problem is that the TCP RFC definition of socket is in conflict with the defintion of socket used by all major operating systems and libraries.

By definition the RFC is correct. When a library misuses terminology, this does not supersede the RFC. Instead, it imposes a burden of responsibility on users of that library to understand both interpretations and to be careful with words and context. Where RFCs do not agree, the most recent and most directly applicable RFC takes precedence.

References

1. TCP-IP Illustrated Volume 1 The Protocols, W. Richard Stevens, 1994 Addison Wesley

2. RFC793, Information Sciences Institute, University of Southern California for DARPA

3. RFC147, The Definition of a Socket, Joel M. Winett, Lincoln Laboratory