JOINS
- SQL JOINS are used to retrieve data
from two or more tables and show that data in a single table on the
basis of the JOIN condition.
- A join is actually performed by the where clause which combines the specified rows of tables.
- In SQL, tables are related to each
other with keys(Primary key, Candidate key, Super key). Primary key is a
column that contain unique value for each row
JOIN: TYPES
- Outer join
- Left outer
- Right outer
- Full outer
- Self join
-
-
- INNER JOIN
INNER JOIN will display all the records that have matched.
Syntax:
Select colname(s) from table_name1 inner join table_name2 using (colname)
Reference Table 1: “student” table * RollNo is the primary Key
| RollNo |
StudentName |
Marks |
| 1 |
abhi |
78 |
| 2 |
sunny |
88 |
| 3 |
Rajesh |
300 |
| 4 |
Rahul |
400 |
Reference Table 2: “project” table * ProjId is the primary Key
| ProjID |
ProjName |
RollNo |
| 3 |
Java Beans |
1 |
| 5 |
Asp.Net |
2 |
| 8 |
Spring |
3 |
| 14 |
Hibernate |
4 |
Example:
SQL> select StudentName, Marks, ProjName from student inner join project using(RollNo);
Output:
| StudentName |
Marks |
ProjName |
| abhi |
78 |
Java Beans |
| sunny |
88 |
Asp.Net |
| Rajesh |
300 |
Spring |
| Rahul |
400 |
Hibernate |
INNER JOIN: TYPES
EQUI JOIN
EQUI JOIN is a join which contains an ‘=’ operator in the joins condition.
Syntax:
Select colname(s) from table_name1, table_name2 where table_name1.colname = table_name2. colname;
Example:
SQL> select StudentName, Marks, ProjName from student, project where student.RollNo= project. RollNo;
Output:
| StudentName |
Marks |
ProjName |
| abhi |
78 |
Java Beans |
| sunny |
88 |
Asp.Net |
| Rajesh |
300 |
Spring |
| Rahul |
400 |
Hibernate |
NATURAL JOIN
Natural join compares all the common columns.
Syntax:
Select colname(s) from table_name1 natural join table_name2;
Example:
SQL> select RollNo, StudentName, Marks, ProjName from student natural join project;
Output:
| RollNo |
StudentName |
Marks |
ProjName |
| 1 |
abhi |
78 |
Java Beans |
| 2 |
sunny |
88 |
Asp.Net |
| 3 |
Rajesh |
300 |
Spring |
| 4 |
Rahul |
400 |
Hibernate |
CROSS JOIN
CROSS JOIN will gives the cross/cartesion product.
Syntax:
Select colname(s) from table_name1 cross join table_name2;
Example:
SQL> select RollNo, StudentName, Marks, ProjName from student cross join project;
Output:
| RollNo |
StudentName |
Marks |
ProjName |
| 1 |
abhi |
78 |
Java Beans |
| 2 |
sunny |
88 |
Java Beans |
| 3 |
Rajesh |
300 |
Java Beans |
| 4 |
Rahul |
400 |
Java Beans |
| 1 |
abhi |
78 |
Asp.Net |
| 2 |
sunny |
88 |
Asp.Net |
| 3 |
Rajesh |
300 |
Asp.Net |
| 4 |
Rahul |
400 |
Asp.Net |
| 1 |
abhi |
78 |
Spring |
| 2 |
sunny |
88 |
Spring |
| 3 |
Rajesh |
300 |
Spring |
| 4 |
Rahul |
400 |
Spring |
| 1 |
abhi |
78 |
Hibernate |
| 2 |
sunny |
88 |
Hibernate |
| 3 |
Rajesh |
300 |
Hibernate |
| 4 |
Rahul |
400 |
Hibernate |
NON EQUI JOIN:
NON EQUI JOIN contains an operator other than ‘=’ in the joins condition.
Syntax:
Select colname(s) from table_name1, table_name2 where table_name1.colname> table_name2. colname;
Example:
SQL> select StudentName, Marks, ProjName from student, project where student.RollNo> project. RollNo;
Output:
| StudentName |
Marks |
ProjName |
| sunny |
88 |
Java Beans |
| Rajesh |
300 |
Asp.Net |
| Rahul |
400 |
Spring |
- OUTER JOIN
Outer join gives the non-matching records along with matching records.
OUTER JOIN: TYPES
LEFT OUTER JOIN
LEFT OUTER JOIN will display the all
matching records and the records which are in left hand side table those
that are not in right hand side table.
Syntax:
Select colname(s) from table_name1 left outer join table_name2 on table_name1.colname= table_name.colname;
Reference Table 1: “student” table * RollNo is the primary Key
| RollNo |
StudentName |
Marks |
| 1 |
abhi |
78 |
| 2 |
sunny |
88 |
| 3 |
Rajesh |
300 |
| 4 |
Rahul |
400 |
Reference Table 2: “project” table * ProjId is the primary Key
| ProjID |
ProjName |
RollNo |
| 3 |
Java Beans |
1 |
| 5 |
Asp.Net |
2 |
| 8 |
Spring |
3 |
Example:
SQL> select StudentName, Marks, ProjName from student left outer join project on student.RollNo= project. RollNo;
Output:
| RollNo |
StudentName |
Marks |
ProjName |
| 1 |
abhi |
78 |
Java Beans |
| 2 |
sunny |
88 |
Asp.Net |
| 3 |
Rajesh |
300 |
Spring |
| 4 |
Rahul |
400 |
|
RIGHT OUTER JOIN
RIGHT OUTER JOIN will display the all
matching records and the records which are in right hand side table
those that are not in left hand side table.
Syntax:
Select colname(s) from table_name1 right outer join table_name2 on table_name1.colname= table_name.colname;
Example:
SQL> select StudentName, Marks, ProjName from student right outer join project on student.RollNo= project. RollNo;
Output:
| RollNo |
StudentName |
Marks |
ProjName |
| 1 |
abhi |
78 |
Java Beans |
| 2 |
sunny |
88 |
Asp.Net |
| 3 |
Rajesh |
300 |
Spring |
|
|
|
Hibernate |
SQL: FULL OUTER JOIN
FULL OUTER JOIN will display the all matching records and the non-matching records from both tables.
Syntax:
Select colname(s) from table_name1 full outer join table_name2 on table_name1.colname= table_name.colname;
Example:
SQL> select StudentName, Marks, ProjName from student full outer join project on student.RollNo= project. RollNo;
Output:
| RollNo |
StudentName |
Marks |
ProjName |
| 1 |
abhi |
78 |
Java Beans |
| 2 |
sunny |
88 |
Asp.Net |
| 3 |
Rajesh |
300 |
Spring |
| 4 |
Rahul |
400 |
|
|
|
|
Hibernate |
- SELF JOIN
Joining the table itself is called self join.
Syntax:
Select colname(s) from table_name t1, table_name t2 where t1.colname=t2.colname;
Reference Table 1: “student” table * RollNo is the primary Key
| RollNo |
StudentName |
ProjID |
Marks |
| 1 |
abhi |
2 |
78 |
| 2 |
sunny |
1 |
88 |
| 3 |
Rajesh |
4 |
300 |
| 4 |
Rahul |
3 |
400 |
Example:
SQL> select t1.StudentName, t2.Marks from student t1, student t2 where t1.RollNo=t2.ProjId;
Output:
| StudentName |
Marks |
| abhi |
88 |
| sunny |
78 |
| Rajesh |
400 |
| Rahul |
300 |
Here I am
going to discuss a simple application to find your search location in the
google map, by street, city, state, zip code wise. You can also search your
location by Latitude and Longitude.
<%@ Page Language="VB"
AutoEventWireup="false"
CodeFile="Default.aspx.vb"
Inherits="_Default"
%>
<!DOCTYPE
html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="300px" cellpadding="2" cellspacing="2" style="border: 1px solid maroon;">
<tr>
<td colspan="2">
<b>Search by
Street, City, State and ZipCode</b>
</td>
</tr>
<tr>
<td>
Street
</td>
<td>
<asp:TextBox ID="txtStreet"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
City
</td>
<td>
<asp:TextBox ID="txtCity"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
State
</td>
<td>
<asp:TextBox ID="txtState"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
ZipCode
</td>
<td>
<asp:TextBox ID="txtZipCode"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="ButtonSearch"
runat="server"
Text="Search"
OnClick="ButtonSearch_Click"
/><br />
</td>
</tr>
</table>
<div>
</div>
<table width="300px" cellpadding="2" cellspacing="2" style="border: 1px solid maroon;">
<tr>
<td colspan="2">
<b>Search by Latitude and Longitude</b>
</td>
</tr>
<tr>
<td>
Latitude</td>
<td>
<asp:TextBox ID="txtLat" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Longitude</td>
<td>
<asp:TextBox ID="txtLong"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="ButtonLatLong"
runat="server"
Text="Search by
Lat Long" OnClick="ButtonLatLong_Click" CausesValidation="false"
/>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Add reference as follows:
Figure 1:
Figure 2:
Imports
System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.IO
Imports System.Text
Imports System.Drawing
Imports System.Windows.Forms
Partial
Public Class
_Default
Inherits System.Web.UI.Page
Private url As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub
ButtonSearch_Click(ByVal sender As Object, ByVal e As EventArgs)
Try
Dim street As String = String.Empty
Dim city As String = String.Empty
Dim state As String = String.Empty
Dim zip As String = String.Empty
Dim queryAddress As New
StringBuilder()
queryAddress.Append("http://maps.google.com/maps?q=")
If txtStreet.Text
<> String.Empty Then
street
= txtStreet.Text.Replace(" "c, "+"c)
queryAddress.Append(street
+ ","c + "+"c)
End If
If txtCity.Text
<> String.Empty Then
city
= txtCity.Text.Replace(" "c, "+"c)
queryAddress.Append(city
+ ","c + "+"c)
End If
If txtState.Text <>
String.Empty Then
state
= txtState.Text.Replace(" "c, "+"c)
queryAddress.Append(state
+ ","c + "+"c)
End If
If txtZipCode.Text
<> String.Empty Then
zip
= txtZipCode.Text.ToString()
queryAddress.Append(zip)
End If
url = queryAddress.ToString()
Response.Redirect(url,
False)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(),
"Unable to Retrieve Map")
End Try
End
Sub
Protected Sub
ButtonLatLong_Click(ByVal sender As Object, ByVal e As EventArgs)
If txtLat.Text = String.Empty OrElse
txtLong.Text = String.Empty Then
MessageBox.Show("Supply a latitude and longitude value",
"Missing Data")
Exit Sub
End If
Try
Dim lat As String = String.Empty
Dim lon As String = String.Empty
Dim queryAddress As New StringBuilder()
queryAddress.Append("http://maps.google.com/maps?q=")
If txtLat.Text
<> String.Empty Then
lat
= txtLat.Text
queryAddress.Append(lat
& "%2C")
End If
If txtLong.Text
<> String.Empty Then
lon
= txtLong.Text
queryAddress.Append(lon)
End If
url = queryAddress.ToString()
Response.Redirect(url,
False)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString(),
"Error")
End Try
End Sub
End
Class
Output:
