کاملترین منابع و سورس پایگاه داده اوراکل Oracle
بیش از 160 صفحه آموزش کامل و جامع اوراکل
بیش از 40 صفحه اسلاید ارایه اوراکل
بیش از 28 سورس اجرایی اوراکل
مراحل آموزش نصب و راه اندازی اوراکل 10 بر روی ویندوز سرور2003
SQL Server: JOIN vs IN vs EXISTS - the logical difference
There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.
This is simply not true. To see why not, let's review what each statement does.
IN:
Returns true if a specified value matches any value in a subquery or a list.
Exists:
Returns true if a subquery contains any rows.
Join:
Joins 2 resultsets on the joining column.
If not read carefully it looks pretty same so far, doesn't it.
The difference comes when you take into account the "dreaded" THREE-VALUED LOGIC.
Let's review that also:
-- this is true
SELECT 1 WHERE 1 = 1
-- this is false
SELECT 1 WHERE 1 = 0
-- this is unknown - it is usually expected to be false, but that only shows
-- misunderstanding of nulls. It's not false it's only treated as false in the filter
SELECT 1 WHERE 1 = NULL
-- this is also unknown - but logicaly it would seem it would be true
-- but unknown compared to unknown equals uknown and it is treated as false in the filter
SELECT 1 WHERE NULL = NULL
The where will return a row only if the condition evaluates to true which UNKNOWN isn't.
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
PS: INNER JOIN is the same as JOIN.________________________________________SQL INNER JOIN Example The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
SAVE my_query
START my_query
@ my_query
EDIT my_query
SET SERVEROUT[PUT] {ON | OFF}
SET AUTOTRACE ON
Enter a slash (/) at the command prompt to re-execute the command in the buffer:
Procedure:
exec discharge_patient(6,3)
Function
exec dbms_output.put_line('Result Count Bed Availabel' || ckeck_bed_availabel(103))
Package:
EXEC Hospital.add_new_patient('Avana',18,'Y',3,4);
ALTER TABLE tbl_Dept_info DROP CONSTRAINT fk_tbl_dept_info ;
ALTER TABLE tbl_ward_info DROP CONSTRAINT fk_tbl_ward_info1;
DROP TABLE tbl_admission_doc_info ;
DROP TABLE tbl_admission_drug_info ;
DROP TABLE tbl_drug_info ;
DROP TABLE tbl_patient_admission ;
DROP TABLE tbl_patient_info ;
DROP TABLE tbl_ward_bed_info ;
DROP TABLE tbl_nurse_info ;
DROP TABLE tbl_ward_info ;
DROP TABLE tbl_doc_info ;
DROP TABLE tbl_Dept_info ;
CREATE TABLE tbl_Dept_info(
Dept_Id NUMBER(5) ,
Specialisation VARCHAR2(20) NOT NULL,
Total_Docs NUMBER(5) NOT NULL,
Dept_Head NUMBER(5) ,
CONSTRAINT pk_tbl_dept_info PRIMARY KEY (Dept_Id)
);
CREATE TABLE tbl_doc_info(
Doctor_Id NUMBER(5) ,
Dept_Id NUMBER(5) ,
Doctor_Name VARCHAR2(20) NOT NULL,
Doctor_Age NUMBER(2) NOT NULL,
Doctor_Phone NUMBER(10) NOT NULL,
Doctor_Avail CHAR(1) CHECK ( Doctor_Avail IN('Y' , 'N')),
CONSTRAINT pk_tbl_doc_info PRIMARY KEY (Doctor_Id),
CONSTRAINT fk_tbl_doc_info FOREIGN KEY (Dept_Id)
REFERENCES tbl_Dept_info(Dept_Id)
);
کاملترین منابع و سورس پایگاه داده اوراکل Oracle