|
Select__ \ sql__ JOIN__
http://www.java2s.com/Code/SQLServer/CatalogSQLServer.htm
http://www.w3schools.com/sql/default.asp?PHPSESSID=300ae3404d5fa2612f238abeebb8869c
|
נושא
|
ORACLE
|
Sql server
|
|
יצירת טבלה
|
CREATE TABLE AVNERTU.MIV_1_TBL
(
AAA_1 VARCHAR2(20 BYTE),
BBB_1 VARCHAR2(20 BYTE),
CCC_1 VARCHAR2(20 BYTE),
DDD_1 VARCHAR2(20 BYTE)
)
|
CREATE TABLE MIV_1_TBL
(
AAA_1 CHAR(20 ),
BBB_1 CHAR(20 ),
CCC_1 CHAR(20 ),
DDD_1 CHAR(20 )
)
|
|
JOIN__
INNER JOIN
|
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL,
MIV_2_TBL
WHERE MIV_1_TBL.ID = MIV_2_TBL.ID
|
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL INNER JOIN MIV_2_TBL
ON MIV_1_TBL.ID = MIV_2_TBL.ID;
|
|
LEFT JOIN
|
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL,
MIV_2_TBL
WHERE MIV_1_TBL.ID = MIV_2_TBL.ID(+)
|
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL LEFT JOIN MIV_2_TBL
ON MIV_1_TBL.ID = MIV_2_TBL.ID;
|
|
RIGHT JOIN
|
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL,
MIV_2_TBL
WHERE MIV_2_TBL.ID =MIV_1_TBL.ID(+)
|
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL RIGHT JOIN MIV_2_TBL
ON MIV_1_TBL.ID = MIV_2_TBL.ID;
|
|
Loop
|
|
Declare @v_counter int
set @v_counter =0
while @v_counter < (select COUNT(1) from sys.sysdatabases)
begin
set @v_counter = @v_counter + 1
print 'The counter is ' + cast(@v_counter as char)
end
|
|
Sql server
|
Oracle
|
|
Tsql:
-- get 2 numbers > multiple them
create PROCEDURE x_proc (@p_n1 int, @p_n2 int)
as
begin
declare @sofi int;
set @sofi =@p_n1 * @p_n2
end;
-- EXEC [dbo].[x_proc] @p_n1 = 3, @p_n2 = 5
|
Pl-sql
CREATE FUNCTION AVNERTU.lsh_number (p_xxx number )
return number
is
v_nisuy number;
begin
v_nisuy:=p_xxx+1;
return v_nisuy;
end;
/
|
|
Tsql:
-- fun to get string - return it !
alter FUNCTION xc_fun (@p_text as varchar (1000) )
returns varchar (1000)
as
begin
declare @v_ret varchar (1000);
set @v_ret=@p_text;
return @v_ret;
end
-- select dbo.xc_fun ('uu') as ddd
|
Pl-sql
CREATE PROCEDURE AVNERTU.isv_nisuy (n1 in number, n2 in number, tot out number)
IS
v_ggg number;
BEGIN
tot:= n1*n2;
v_ggg:=(n1*n2) * 2;
END isv_nisuy;
/
|
|
Tsql
select distinct emplyee, rank_id
from employees_tbl
|
Pl-sql
declare
v_ndd varchar (20);
begin
v_ndd:= 'sss';
end ;
|
|
Tsql
select emplyee, rank_id
from employees_tbl
group by emplyee, rank_id
|
Pl-sql
SELECT MIV_1_TBL.ID,
MIV_2_TBL.BBB_2,
MIV_2_TBL.CCC_2,
MIV_2_TBL.DDD_2
FROM MIV_1_TBL,
MIV_2_TBL
WHERE MIV_1_TBL.ID =MIV_2_TBL.ID(+)
-- MIV_1_TBL.ID = full tabe
|
|
tsql
declare
@v_ndd varchar (20);
begin
set @v_ndd = 'sss';
end
|
|
|
tsql
FROM [test1].[dbo].[UserHistory] h
inner join
[test1].[dbo].[Users] z
on h.[userid]=z.[userid]
from mtv_1_tbl left join mtv_2_tbl
on mtv_1_tbl = mtv_2_tbl
--mtv_1_tbl = full
from mtv_1_tbl right join mtv_2_tbl
on mtv_1_tbl = mtv_2_tbl
--mtv_2_tbl = full
|
|
|
