Assalamualaykum Wr Br…I was looking into one value in database for a table named Customer which have column as Gender. The value in column should be either M or F or Null, but cannot have any other value.System throws exception when there is a mismatch of the value specified above.
The data of the table is fed from file.Before Customer table, data is first stored in temporary table then certain validations then moved to customer table. My Client stated that Gender value in file was null and it is temporary table which is showing as null but does not go in Customer acutal table.
When I queried in data as
select * from customer_temp where gender is not null
gives me result set, if I use where gender is null then does not provide result.
Inorder to find the exact value in gender column, I used the following query:
select ASCII(gender) from customer_temp where gender is not null
The above query displayed the value as 13 which is value of carriage return(\n).
Today we explore the simply quite simple and interesting concept of SQL joins.
Join is a clause which is used to combine rows from two or more tables based on a common field between/among them.
The most common join is INNER JOIN, before we start up, lets create two tables as shown below:
To clear the screen in SQL ORACLE type: cl scr(press Enter).
Now lets insert some data in these tables as shown below:
Similarly add some data into orders table as shown:
Now I recapitulate again that we have two tables named “ORDERS” and “CUSTOMERS”. I retrieve the data from two tables but use single from clause thats because of joins. I retrieve OrderId from order table aliases as O, CustomerName from Customers table aliases as C and O.Orderdate as shown:
The output which we got was based on the constraint customerid is equal in both tables(CUSTOMERS,ORDERS). When the constraint is satisfied the CUSTOMERS Table fields are populated.
What else would happen if I dont specify Inner in select query which does produces the same output as above shown. We conclude that both INNER JOIN AND JOIN ARE SAME.
If we replace * instead of o.orderid,c.customer and o.orderdate then we get the result set as:
It returns all combination of orders tables rows and customers tables rows based on constraint match.
INNER JOIN: Returns all rows when there is at least one match in BOTH tables.It means in first we got 3 rows as output now we get these 3 rows two times as we joined the two tables.
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.Observe the following screen:
One thing to note is that left join returns all rows from left table and returns only matched rows from right table.In some databases LEFT JOIN is called LEFT OUTER JOIN.
The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).
The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.