SQL: How to choose Table name and Column name

Say for example you have two table Customer and Supplier. Both table have surname and forename field. Now you need a query to select the name of the Customer and Supplier also.So, in order to distinguish between Customer surname, forename and Supplier surname, forename you have to name the filed in such a so that you understand it is Customer surname or it is Supplier surname.

To achieve this we can start with FOUR CAPITAL letter what will be the short name of the table and followed by the table full name. For example: We can name the Customer table as CUSTcustomer and supplier table as SUPPsupplier.

After creating table we can start the field name of the table with the chosen FOUR CAPITAL letter short name of the table followed by under score sign followed by field name. For example: surname of the Customer table can be CUST_Surname and surname of the Supplier table can be SUPP_Surname.

Now if we run a query and from the result sheet we will be able to distinguish between Customer surname and Supplier surname.

See picture bellow:

SQL Join Example: Inner join Vs Left Join Vs Full Outer Join

Source: http://johnragan.wordpress.com/2009/11/16/inner-join-vs-left-outer-join-vs-right-outer-join-vs-full-outer-join-examples/

Inner Join vs. Left Outer Join vs. Right Outer Join vs. Full Outer Join Examples

I write SQL queries (and joins) every once in a while, but never had to deal with such things as “left outer joins” or “full outer joins” and so forth.  I always found this a little confusing and could not remember the differences after reading about them.

Today I had a crazy idea – why not actually TRY using the different queries and see what happens?  Consider these tables:

Table A

id	common	name
1	a	Name 1
2	b	Name 2

Table B

id	common	title
1	b	Title 1
2	c	Title 2

Here is what I found when I do various joins of table A to table B on a common column:

• Inner Joins are the same as the regular joins you have been doing for a while.  I replaced my query’s ‘join’ statement with ‘inner join’ and got the same result (as well as reading that these were the same).  Note that this means that any rows from either table which do not intersect are not part of the result.  The remaining joins address this.
  The query: select * from table_a inner join table_b where table_a.common = table_b.common;

Inner Join A and B

a.id	a.common	a.name	b.id	b.common	b.title
2	b	Name 2	1	b	Title 1

• Left Outer Joins are the same as an Inner Join of A and B, PLUS all the rows from A that did not match a row from B, with all the fields from the B side set to NULL for those rows.  Think of A as being on the left side.
  The query: select * from table_a left join table_b on table_a.common = table_b.common;

Left Outer Join A and B

a.id	a.common	a.name	b.id	b.common	b.title
1	a	Name 1	NULL	NULL	NULL
2	b	Name 2	1	b	Title 1

• Right Outer Joins are the same as an Inner Join of A and B, PLUS all the rows from B that did not match a row from A, with all the fields from the A side set to NULL for those rows.  Think of B as being on the right side.
  The query: select * from table_a right join table_b on table_a.common = table_b.common;

Right Outer Join A and B

a.id	a.common	a.name	b.id	b.common	b.title
2	b	Name 2	1	b	Title 1
NULL	NULL	NULL	2	c	Title 2

• MySQL does not support Full Outer Joins natively (there are some work-arounds).  However, it is roughly like the union of Left Outer Joins and Right Outer Joins according to what I have read.  Seehttp://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/

Full Outer Join A and B

a.id	a.common	a.name	b.id	b.common	b.title
1	a	Name 1	NULL	NULL	NULL
2	b	Name 2	1	b	Title 1
NULL	NULL	NULL	2	c	Title 2