Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
21 Cards in this Set
- Front
- Back
- 3rd side (hint)
Types of joins
|
horizontal,
inner, outer, left outer, right outer, full, natural, carteisan, self(reflexive) |
|
|
equijoin
|
uses equality to determine matching rows
|
|
|
join-keys
|
ON or WHERE
|
|
|
Inner join
|
return only matching rows. syntax: proc sql; select * from customers, transactions where customers.ID=transactions.ID; quit;
|
|
|
outer joins
|
return all matching rows plus nonmatching rows from one or both tables (union)
|
|
|
Carteisian join
|
also called a cross join.
one row for every combination of rows from two tables (number of rows in table A) x (nubmer of rows in table B). example 3 x 3 = 9. Each column from each table is represented in the join. Matching columns appear in the join for the number of tables being joined. If both Table A and Table B have "ID", the join will have two "ID" columns. |
|
|
Natural join
|
automatically selects columns with same name and type for determining matching rows. (Inner by default by could be outer)
|
|
|
Number of rows in the result of joins
|
inner join is less than or equal to outer join less than or equal to Cartesian product
|
|
|
Qualification
|
the specification of the table names in the column reference in the where clause is called "qualification"
|
|
|
Alias
|
the table name may be abbreviated with an alias.
Syntax: proc sql; select * from customers as c, transactions as t where c.ID=t.ID; quit; |
|
|
Difference between SQL join and datastep merge
|
datastep merge requires a proc sort for both tables before merging. also finds first occurrence of the match then creates observation, then moves pointer to next obs in both datasets. Also automatically overlays columns. PROC SQL selects ALL (combinations of) rows with matching values of ID, does not overlay columns.
|
|
|
Alternative syntax for label
|
select employee_name as Employee Name
|
or put in quotes:
|
|
month(Birth_Date)
|
will return the month number out of the sequence from Bith_Date
|
|
|
What will this do? ORDER BY 4
|
This will sort the report in ascending order of values in the fourth selected column in the select statement. quick way to order rather than typing it out.
|
|
|
Alternative (more descriptive) inner join syntax
|
PROC SQL; TITLE "BROUGHT TO YOU BY SQL ALTJOINSYNTAX"; SELECT * FROM tableA as A INNER JOIN TableB as B ON A.ID=B.ID; QUIT;
|
|
|
natural joins
|
Adopts columns with same name, same type as join-keys. Includes all columns, only one of the matching columns and only rows with matching values on the matching column. syntax example: PROC SQL; TITLE "You make me feel like a natural join"; SELECT * FROM tableA NATURAL JOIN tableB; QUIT;
|
|
|
Outer join - Left Join
|
Will include all rows from the first (or left table) and all columns from the other table associated with any value that matches that in the matched column of the left table. syntax: proc sql; title "left join (all customers); selct * from customers as c LEFT JOIN transactions as t on c.id=t.id; quit;
|
|
|
Outer join - Right Join
|
Will include all rows from the second table (or left table) and all columns from the other table associated with any value that matches that in the matched column of the right table. syntax: proc sql; title "right join (all customers); selct * from customers as c RIGHT JOIN transactions as t on c.id=t.id; quit;
|
|
|
Outer join - FULL JOIN
|
will include all columns of each table. Matching columns will be included for each table. syntax: proc sql; select * from customers as c FULL JOIN transactions as t on c.id=t.id; quit;
|
|
|
Coalesce
|
returns the first non-missing argument. All arguments must be same type. When all are missing, missing value is returned. Basically, it will combine matching columns. syntax: proc sql; select coalesce(c.id,t.id) as id, name, action, amount from customers as c FULL JOIN transactions as t on c.id=t.id; quit;
|
|
|
Comparison of join types
|
table limit: 256 for both inner and outer. join behaviour: inner-returns matching rows, outer-returns matching and nonmatching rows. Join OPtiosn: Inner-Matching rows only, Outer-LEFT, FULL, RIGHT. Syntax changes: Inner-Multiple tables separated by commas in the FROM clause & Where clause that specifies join criteria, Outer-ON cluase that specifies join criteria
|
|