SQL - Structured Query Language

SQL Joins

SQL joins are used to retrieve data from two or more tables based on logical relationships between the tables. A typical join condition specifies a foreign key from one table and its associated key in the other table primary key. Logical operators are used to comparing values from the associated columns.

Note: For Demo, create DEPT and EMP Tables and then INSERT sample records.

-- Sample Statements --

CREATE TABLE DEPT

(

Dept_Code numeric(5) Primary Key,

Dept_Name varchar(50) Not Null,

)

CREATE TABLE EMP

(

Emp_Code numeric(5)Primary Key,

Emp_Name varchar(50) Not Null,

DOJ datetime Default GetDate(),

Salary numeric(7,2) Check (salary > 1000 and Salary < 5000),

Phone varchar(50),

Dept_Code numeric(5) References DEPT(Dept_Code)

On Delete Cascade

)

Query Output Screen

Note: Tables cannot be joined directly on ntext, text, or image columns. However, tables can be joined indirectly on ntext, text, or image columns by using SUBSTRING.

For example, SELECT * FROM t1 JOIN t2 ON SUBSTRING(t1.textcolumn, 1, 20) = SUBSTRING(t2.textcolumn, 1, 20) performs a two-table inner join on the first 20 characters of each text column in tables t1 and t2.

In addition, another possibility for comparing ntext or text columns from two tables is to compare the lengths of the columns with a WHERE clause, for example: WHERE DATALENGTH(p1.pr_info) = DATALENGTH(p2.pr_info)

* * * * *

Your Comment To AUTHOR