Visual Explanation of SQL Joins

     SQL Joins are used to relate information in different tables. In short and crisp, there are 4 types of SQL joins. Inner, Left Outer, Right Outer and Full joins. Let’s see the differences between these four using Venn diagrams and then can see the SQL commands.

     Let’s say we have two sets of data in our relational database. Table A and Table B with some sort of relation specified by primary and foreign keys. The result of joining these tables together can be visually represented by following diagram:

See how each of the joins are represented.

Select all records from Table A and Table B, where the join condition                                                   is met.

Select all records from Table A, along with records from Table B for which                                                                 the join condition is met.

Select all records from Table B, along with records from Table A for which                                                                  the join condition is met.

Select all records from Table A and Table B, regardless of whether the join                                                                 condition is met or not.

Let's see them now in Tables & SQLs representation. If table A and table B exist like below.

Inner Join:

SQL Query is:

SELECT * FROM TableA 

INNER JOIN TableB ON TableA.Name = TableB.Name;

Result will something look like...

Left Outer Join:

SQL Query is:

SELECT * FROM TableA 

LEFT OUTER JOIN TableB ON TableA.name = TableB.name;

Result will something look like...

Right Outer Join:

SQL Query is:

SELECT * FROM TableA 

RIGHT OUTER JOIN TableB ON TableA.name = TableB.name;

Result will something look like...

Full Join:

SQL Query is:

SELECT * FROM TableA 

FULL JOIN TableB ON TableA.Name = TableB.Name;

Result will something look like...

     Likewise, you can create different types of joins just by adding few additional conditions to the SQL. For example, you can make Full Join as Full Outer Join just by adding a condition. 

     SQLs mentioned here are in ANSI 92/99 syntax. One of the good reasons to use ANSI syntax over the old conventional(+) join syntax is that, there are nil chances of accidentally creating a cartesian product. With more number of tables, there is a chance to miss an implicit join with older join syntax, however, with ANSI syntax you cannot miss any join as you must explicitly mention them. 

Happy Learning.. :) 

How To Escape Special Characters in Writing SQLs!

Oracle DB reserved below characters with specific meaning and purpose.

1. _ (underscore) wildcard character is used to match exactly one character
2. % (percentage) is used to match zero or more occurrences of any character
3. ‘ (apostrophe, quote) is used to mark the value supplied

     If you want to insert the data with any of these characters or when performing string search with LIKE keyword, they are not interpreted literally and cause errors. One should escape them if these need to be interpreted as part of string value instead of a pre-defined meaning.

Let’s look at how each of these characters can be escaped.

Escape wildcard characters:

The LIKE keyword allows for string searches. As mentioned earlier, _ is used to match exactly one character.

1 2	SQL> SELECT name FROM emp WHERE id LIKE '%/_%' ESCAPE '/'; SQL> SELECT name FROM emp WHERE id LIKE '%\%%' ESCAPE '\';

Escape quotes('):

Quotes can be escaped in two ways. Let’s look at each.

•      Use another quote to escape one quote. For example,

1 2 3 4 5

SQL> SELECT 'VENKI''S' AS WHOS FROM DUAL; WHOS ------- VENKI’S

Another example is... 

1 2 3 4 5

SQL> SELECT 'VENKI''S LAPTOP: ''''VAIO''''' AS WHATS FROM DUAL; WHATS --------------------- VENKI’S LAPTOP: ''VAIO''

•        Use q expression. For example

1 2	SQL> SELECT q'[VENKI'S]' AS QUOTE_S FROM DUAL; SQL> SELECT q'[VENKI'S LAPTOP: ''VAIO'']' AS QUOTE_S FROM DUAL;

Escape Ampersand (&):

This can be escaped in 3 ways. Let’s look at each.

•        Use DEFINE. For example,

1 2 3

SQL> CREATE TABLE temp (name VARCHAR2(30)); SQL> SET DEFINE ~ SQL> INSERT INTO temp VALUES('Tom & Jerry');

Instead of ~, you can use ON or OFF to turn it on or off.

•        Use ESCAPE. For example,

1 2	SQL> SET ESCAPE '\' SQL> SELECT 'Tom \& Jerry' FROM DUAL;

You can define any other character also as escape character.

•        Use SCAN. For example,

1 2	SQL> SET SCAN OFF; SQL> SELECT 'Tom & Jerry' FROM DUAL;

But, this has been made obsolete. Please see for some more obsolete commands in Oracle DB https://docs.oracle.com/cd/B19306_01/server.102/b14357/apc.htm

•        Another best way is to use to concatenation to escape the &.

1	SQL> SELECT 'Tom ' || '&' || ' Jerry' FROM DUAL;

Happy Learning… J