Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

      A  B  C  D  E  F  G  H  I  J  K  L  M
     N  O  P  Q  R  S  T  U  V  W  X  Y  Z

Index Column

Index

3 Phase Power

7-Segment Code
7-segment display - ladder logic

74XXX TTL Data Sheets

A


abampere

abbreviations:
     Electrical Engineering Abbreviations and Acronyms
     Military Abbreviations and Acronyms

ABCD matrices for transmission lines

Absolute Pressure

Absolute Viscosity

absorption spectra

acids - pH of common inorganic acids

activity series of metals

adder:
     full-adder
     half-adder

air composition

Air Pressure Loss For Every 100 Feet of Clean Commercial Steel Pipe

American National Standard Letter Designation for Welding and Allied Processes (ANSI/AWS A2.4-9I)

American Standard Code for Information Interchange (ASCII)

American Wire Gauge (AWG)

Ampacity of Copper and Aluminum Insulated Wire

ampere

Ampere's Circuital Law

amplifier - General purpose Amplifier

amplifier - general purpose inverting amplifier

angle between two vectors

Angular Speed Formula

Angular Velocity Formula

apparent power

area of two dimensional shapes

area unit conversion calculator

Atmospheric Pressure at Altitudes Above Sea Level

AutoCAD Notes

AutoDesk AutoCAD Notes

automation

AWG (American Wire Gage)



battery technology

beam:
     structural beams

Beam Deflection

Beam Design and Analysis for Moment and Shear

Belt:
     V-belt:
          Commercially available "5V" Sheave Pitch Diameter
          dimensions
          sizing chart
          Belt Reduced Power Capability with Contact Angle

Bernoulli's Law

bevel gear

Blot-Savart Law for Magnetic Field & Lorentz Force Equation

Bleeder Valve

blinking lamp circuit - ladder logic

bolt: Eccentrically Loaded Bolts

bolt and nut materials

bolt technical reference guide

Boltzmann constant

Bonding between atoms and molecules

boolean algebra summary

Boundary Conditions

Boyle's Law

BSPT (British Standard Pipe Thread)

BSPP (British Standard Parallel Pipe)

Bulk Modulus of Common Substances

Buoyancy Force

 

camera: contact lens camera

camera: vision system cameras

Capacitance, Conductance, and Inductance

Capacitor

capacitor energy storage

Cartesian Coordinate System

Cartesian, Cylindrical, Spherical Conversions

centroids of two dimensional shapes

Ceramics

chain pitch selection chart for roller chains

chain sprocket and platewheels

Charge Distributions

charge of an electron

charge of a proton

Charles's Law

Chebyshev Frequency Response for n=4

Check Valve

circle

circuit breaker

circuit components

co-function identities

codes:
     2 out of 5 code
     7-Segment Code
     ASCII
     Gray Codes

Coefficients of Friction

Coefficients of Linear Thermal Expansion

Column Design and Analysis

Combined Stresses, Axial, Bending, Shear: Mohr's Circle

comparator

Comparison of Properties of Straight Tungsten Carbides and Tool Steels

complex trigonometric identities

complex numbers

Complex Numbers and Phasor Techniques

compressor

Compton scattering

connectors

connectors - modular connectors

Connections: Bolted and Welded

contact configurations

contact lens camera

contactor

Continuity Equation in Fluid Mechanics

Continuity Equation in Electromagnetism

conversions (number base):
     binary to decimal conversion
     binary to hexadecimal conversion
     binary to octal conversion
     decimal to binary conversion
     decimal to hexadecimal conversion
     decimal to octal conversion
     hexadecimal to binary conversion
     hexadecimal to decimal conversion
     octal to binary conversion
     octal to decimal conversion

conversions between two-port network parameters

Coordinate Systems in Electromagnetism

copper tubing
     dimensions of type K copper tubing

Coulomb's Law

CREO Parametric

cross product

Crystal structures for common metals at room temperature

curl operator

cylinder (pneumatic or hydraulic)

Cylindrical Coordinate System

Cylindrical, Spherical, Cartesian Conversions


D Latch

Dalton's Law of partial pressure

Darcy's Formula

data sheets

DC and AC Circuits

de Broglie Theorem

decibel dB

Decimal Equivalent Chart

decoder - ladder logic

Deflection in Beams

demultiplexer

density unit conversion calculator

dielectric constants of selected materials

dielectric strengths of selected materials

Dielectrics as related to Displacement Flux Density

differential operators

Differential Unit Vector in Cartesian Coordinate System

Differential Unit Vector in Cylindrical Coordinate System

Differential Unit Vector in Spherical Coordinate System

Dimensions of Steel Pipe

Dimensions of Steel Valve and Steel Fittings

diode

diode - Light Emitting Diode (LED)

diode - Schmitt Trigger

diode: Voltage drop across a diode

Directional Valve

distance between point and line

distance between point and plane

Divergence

divergence operator

Divergence Theorem

double angle formulas

dot product

drill bit size conversions

Ductility of Selected Materials Measured as Percent Elongation

earth

Eccentrically Loaded Bolts

efficiency

elastic modulus for selected materials

elastomer: Hardness Scale comparison table of metals and elastomer

electric charge

electric current

Electric Field Intensity

Electric Stored Energy Density

Electrical Metallic Tubing - EMT Sizes, Weights, and Dimensions

Electromagnetic Spectrum

Elements of the Periodic Table

EMT Conduit Sizes, Weights, and Dimensions

encoder

energy storage in a capacitor

Energy Storage in Electric and Magnetic Fields

energy unit conversion calculator

equation of a plane

equations

Euler identity

even-odd identities

Exclusive OR (XOR) vs Exclusive NOR (XNOR)

expansion for hyperbolic functions

expansion for sine, cosine, tangent, cotangent

fans as applied to electrical enclosures

Faraday's Law

Faraday's Law and Ampere's Circuital Law in differential Form

fastener: Thread Series Designation

fastener technical reference guide

FDA:

21 CFR 211 - CURRENT GOOD MANUFACTURING PRACTICE FOR FINISHED PHARMACEUTICALS

FlexiBowl

flip-flop using Omron KEEP instruction

Flow Curve and Typical Values of Strength Coefficient K and Strain Hardening Exponent n for Selected Materials

Flow of Air Through Schedule 40 Steel Pipe

Flow of Water Through Schedule 40 Steel Pipe

Fluid Sealing Material Compatibility Chart

force unit conversion calculator

fractions to decimal conversion table

Frequency Allocation Chart in the US (pdf)

full-adder - ladder logic

Gauss' Law

Gauss' Law derived from Conservation of Charge and Ampere's Law

GD&T (Geometric Dimensioning and Tolerancing)

gear:
     bevel gear
     Gear Types

General Energy Equation (Fluid Mechanics)

Geneva drive

geometric tolerancing

Geometry

golden ratio

golden rectangle

Gradient in Cartesian, Cylindrical and Spherical Coordinates

gradient operator

Gray Code:
     Gray Codes
     how to generate gray codes

Greek alphabet

grippers

parallel grippers

needle grippers (overview)

H Beams & Wide Flange ASTM-A36/A572

H-Bridge

Hagen Poiseuille Law for Laminar Flow

half angle formulas

half-adder - ladder logic

Hamming Codes

Hand Pump System

Hardness Scale comparison table of metals and elastometers

Hazen-Williams Constants for Various Materials

head, Velocity

hexagon

HMI (Human Machine Interface)

hydraulic Horsepower

hydraulic Radius

hydraulic schematic symbols

hyperbolic functions

I Beams ASTM-A36/A572

identities:
     co-function identities
     complex trigonometric identities
     euler identity
     even-odd identities
     Pythagorean identities
     quotient identities
     reciprocal identities
     trigonometric identities
     vector identities

image parameters for T and pi networks

inductor

Inequalities

Infrared Spectrum

insulation: Relationship between an Insulator's K-factor, C-factor, and R-factor

integral tables

intensity of a sound wave

interlock circuit in Ladder Logic

Internal Fluid Pressure (psi) on Tubing

inverter

JK Flip Flop

Junior Beams ASTM-A36

Jupiter

kinematic equations

Kinematic Viscosity

Kirchhoff's Current Law

Ladder Logic Circuits

laminar flow: Hagen Poiseuille Law for Laminar Flow

Laplace Transforms

Laplace's Equation

Laplacian operators

Laplacian of a Scalar Function and a Vector

Latches

Law of Conservation of Charge

Law of cosines

length unit conversion calculator

Lens Types

Lenz's Law

Light Emitting Diode (LED)

Limits - Calculus

List of Natural Elements of the Periodic Table

logarithmic constant

logic functions - ladder logic

logic gates

Lorentz Force Equation

low-pass RC filter

Magnetic Field--Ampere's Law, Biot-Savart Law

Magnetic Field Intensity

Magnetic Flux Density

magnetic force on a moving charge

Magnetic Stored Energy Density

Magnetic Vector Potential

mass/weight unit conversion calculator

mathematical symbols

matrices

Maxwell's equations

mechanical power

Mechanics

mechanisms

Melting Points for Some Common Metals and Alloys

mesh sizes: Strainer Mesh Sizes and Size of Solids Removed When Strainer is Clean

Metals

metals: Hardness Scale comparison table of metals and elastometers

Mesh Size Chart

Microwave Oven (pdf)

millimeters to decimal conversion table

modular connectors

Modulus of elasticity for selected materials

modulus of rigidity

moment of inertia

moment of inertia transfer formula

momentum

Moody Friction Factor Chart

MOSFET:
     N-Channel MOSFET

Motion Control Using Servo and Stepper Motors

Motorola microcontrollers

motors:
     electric motor family tree
     classification of Motors by application
     NEMA classification of Motors
     PWM - pulse width modulation
     Sizing a Motor for Hydraulic Pump
     Standard Electric Motor Sizes
     DC Motor - Compound Wound

multiplexer

Multiplication Table (1 thru 12)

 

NAND (Diode-Transistor Logic)

needle grippers (overview)

NEMA Enclosure Ratings

NEMA Motor Frame Sizes

Neutralization Number

Newton's laws of motion

network functions: Types of Network Functions

NOR (Diode-Transistor Logic)

NPN Transistor

NPT (National Pipe Thread)

NPS (Nominal Pipe Size) - Straight pipe sizes

nut:
     bolt and nut materials

Ohm's Law

OSHA - Safeguarding Equipment and Protecting Employees from Amputations

PanelView: How to connect to PC

parameters:
     image parameters for T and pi networks

Pascal's Law

Permeability of a vacuum (or free space)

pi networks: image parameters for T and pi networks

pipe: dimensions and properties of schedule 40 and 80

Pipe Fitting Dimensions

pipe thread types

periodic table: List of Natural Elements of the Periodic Table

PLA

Plank's Law

plastics

PLC (Programmable Logic Controller)

PNP transistor

Poisson's Equation

Poisson's ratio

polar moment of inertia

Polarization

polygon

polymers

Potential Function for Static and Non Static Fields

power consumed by a resistor

Power developed by the product of the force and velocity

power definition

power - Mechanical Power

power-reducing/half angle formulas

power series representation

power:
     electrical apparent power

power transmitted by a shaft given a torque and speed of a shaft

power unit conversion calculator

powers of two

pressure (psi) conversion factors

Principle of Reciprocity

pressure: Atmospheric Pressure at Altitudes Above Sea Level

pressure: Internal Fluid Pressure (psi) on Tubing

product-to-sum formulas

proximity sensor - inductive

PTC CREO Parametric Notes

pump system: Hand Pump System

PWM (pulse width modulation)

Pythagoras' Theorem

Pythagorean identities

Pythagorean Triples

 

quadratic formula and proof

quotient identities

radius of gyration

range equation

radian measure

RC Time Delay

reciprocal identities

regeneration circuit

Relationship between Shear stress and Tensile stress

relay

relief valve

resistivity

Reynold's Equation

robots - Types of Industrial Robots

Rockwell Hardness Scales

ROM (Read Only Memory)

rubbers

safety - machine safety

safety - standards for machine safety

Sanitary Fittings - Tri-Clamp

Saybolt Universal Viscosity

scalar product of two vectors

Schedule 40 pipe: Working Pressure In PSI (psi) in Standard Pipe, Extra Heavy Pipe, Double Extra Heavy Pipe

Schmitt Trigger Diode

Schrödinger's equation

Scissor Lift Mechanism

screw materials

screw tapping and clearance chart

screw thread pitch

screw thread series designations

screw thread types

seals: Fluid Sealing Material Compatibility Chart

sensors

seal in contact in Ladder Logic

Shear and Moment Diagrams by the Summation of Areas

shear strain

Shear Strength of Materials

shear stress analysis on a bolt in single shear

shear stress: Relationship between Shear stress and Tensile stress

sheetmetal gauge sizes and weight/ft^2

SI prefixes

Sieve Opening Size Chart

Sizing a Motor Horsepower (HP) for Drilling, Tapping, and Threading

Sizing a Motor horsepower (HP) for Hydraulic Pumps

Sizing a motors horsepower (Hp) and torque for turning a solid round disk

Skin Depth of Copper

Slider-Crank connecting rod angular speed and acceleration

software notes

solar system

spacecrafts

spark plug

Specific Gravity

specific gravity of gases

specific gravity of liquids

Specific Roughness and Hazen-Williams Constants for Various Materials

Specific Weight

Spectrum: Electromagnetic Spectrum

Spectrum: Infrared Spectrum

speed of sound

speed unit conversion calculator

Spherical Coordinate System

Spherical, Cylindrical, Cartesian Conversions

sprockets and platewheel types

SR Latch

SR Master-Slave Flip-Flop

Stainless Steel Chemical Composition

Standard Electric Motor Sizes

standards for machine safety

Stefan-Boltzmann Law

Stepper Motor Wiring Diagrams

Stokes' Theorem

straight pipe sizes - NPS

strain hardening exponent: Flow Curve and Typical Values of Strength Coefficient K and Strain Hardening Exponent n for Selected Materials

Strainer Mesh Sizes and Size of Solids Removed When Strainer is Clean

strength coefficient: Flow Curve and Typical Values of Strength Coefficient K and Strain Hardening Exponent n for Selected Materials

Strength of Materials

Stress-Strain Curves (pdf)

Stress Summary

Stresses in Beams and Built up Sections and Bolt Spacing

Structural Members

summing junction amplifier

sum or difference of two angles

sum-to-product formulas

sun

sun spot

symbols - electrical symbols

symbols - mathematical symbols

synchronous belt

T networks:
     image parameters for T and pi networks

Tangent Line Problem

tap drill reference

temperature unit conversion calculator

tensile strength: Yield Strength and Tensile Strength for Selected Metals

thermal conductivities of various media

thermal expansion

thermal expansion: Coefficients of Linear Thermal Expansion

Thread Series Designations

thread types - screw

thread types: pipe thread types

time delay - RC Time Delay

timing belt

toggle output with a single pushbutton - ladder logic

toggle output using single pushbutton with different TON and TOFF delays - Ladder Logic

Tonnage for Hole Punching

tool steel:
     Comparison of Properties of Straight Tungsten Carbides and Tool Steels

tool steels

Torricelli's Theorem

Torque Couplings

torque unit conversion calculator

torsion

torsional deflection of a shaft

transformer

transistor - NPN

transistor - PNP

Transmission Line - Parallel Plate

transmission line table of equations(pdf)

Transmission Line Terminated by Resistive Load

Transmission Line with Discontinuity

Tri-Clamp Sanitary Fittings

triangle

trigonometric functions

trigonometric identities

trigonometric values for common angles

Triple Scalar Product

tubing: Dimensions of Type K Copper Tubing

tungsten carbide:
     Comparison of Properties of Straight Tungsten Carbides and Tool Steels

Two-Phase Alternating Current

Two-Port Network Conversions

Types of Network Functions

unit circle

Unit Vectors: Cylindrical & Cartesian, Spherical & Cartesian

USB - Universal Serial Bus

V-belt: Commercially available "5V" Sheave Pitch Diameter V-Belts

V-belt dimensions

V-belt sizing chart

V-Belt Reduced Power Capability with Contact Angle

valve: Bleeder Valve
valve: Check Valve
valve: Directional Valve
valve: Relief Valve

vector:
     angle between two vectors
     vector cross product
     scalar product of two vectors
     vector identities

viscosity

viscosity Index

vision cameras

vision systems

voltage

voltage - worldwide voltages

voltage divider

Voltage drop across a diode

voltage follower

voltage peak detector

voltage regulator

volume unit conversion calculator

wedge physics

weight/mass unit conversion calculator

Welding Filler Metals For Various Aluminum Alloys

welding processes

welding symbol: Location of Elements of a Welding Symbol

Wide Flange & H Beams ASTM-A36/A572

Wien's Law

Wire:
     American Wire Gauge (AWG)
     Ampacity of Copper and Aluminum Insulated Wire

Working Pressure In PSI (psi) in Standard Pipe, Extra Heavy Pipe, Double Extra Heavy Pipe

worldwide voltages

Exclusive OR (XOR) vs Exclusive NOR (XNOR)

Yield Strength and Tensile Strength for Selected Metals

Young's modulus of elasticity


 
 
 

Decoilers

 

Retrieving Data From More Than One Table


The examples presented up to now in this chapter have illustrated the essential features of simple SELECT statements with data retrieval from single tables. However, much of the power of SQL lies in the ability to perform joins through a single statement, i.e. to select data from two or more tables, using the search condition to link the tables in a meaningful way.

The Join Condition

In retrieving data from more than one table, the search condition or join condition specifies the way the tables are to be linked. For example:

List the product name in addition to the EAN and price:
 SELECT product, ean_code, price   FROM items       JOIN products ON items.product_id = products.product_id;     

The join condition here is ITEMS.PRODUCT_ID = PRODUCTS.PRODUCT_ID, which relates the product identifier in table ITEMS (where codes are listed) to the product identifier in table PRODUCTS (where names are listed).

Returns:

 PRODUCT  
EAN_CODE
PRICE
 100 Anos  
77774238724
9.98
 12 Golden Country Greats  
75596190923
17.98
 12 Super Exitos  
724385487521
9.98
 1492: Conquest of Paradise  
75678243226
17.98
 ...  
...
...

Conceptually, the join first establishes a table containing all combinations of the rows in PRODUCTS with the rows in ITEMS, then selects those rows in which the two PRODUCT_ID values are equal. See Conceptual Description of the Selection Process for a fuller description of the conceptual SELECT process.

This does not necessarily represent the order in which the operations are actually performed; the order of evaluation of a complex SELECT statement is determined by the SQL optimizer, regardless of the order in which the component clauses are written.

Cross Products

Without the join condition, the result is a cross product of the columns in the tables in question, containing all possible combinations of the selected columns, for example:

 SELECT product, ean_code, price   FROM items CROSS JOIN products;  

or

 SELECT product, ean_code, price   FROM items, products;  
Returns:

 PRODUCT  
EAN_CODE
PRICE
 'Murder In The Cathedral'  
77774238724
9.98
 'Murder In The Cathedral'  
75596190923
17.98
 'Murder In The Cathedral'  
724385487521
9.98
 'Murder In The Cathedral'  
75678243226
17.98
 ...  
...
...

It is easy to see that a carelessly formulated join query can produce a very large result table. Two tables of 100 rows each, for instance, give a cross product with 10,000 rows; three tables of 100 rows each give a cross product with 1,000,000 rows!

The risk of generating large (erroneous) result tables is particularly high in interactive SQL (e.g. when using Mimer BSQL), where queries are so easily written and submitted.

Simple Joins

In simple joins, all tables used in the join are listed in the FROM clause of the SELECT statement. This is in distinction to nested joins, where the search condition for one SELECT is expressed in terms of another SELECT, see Nested Selects.

Example
 SELECT product, ean_code, price   FROM items       JOIN products ON items.product_id = products.product_id;  

SELECT *

The form SELECT * may be used in a join query, but since this selects all columns in the result set, at least one column is often duplicated (a join condition column).

Example
 SELECT *   FROM items       JOIN products ON items.product_id = products.product_id;  
Returns:

Columns from ITEMS:

 ITEM_ID  
 PRODUCT_ID  
 FORMAT_ID  
 RELEASE_DATE  
 STATUS  
 PRICE  
 STOCK  
 REORDER_LEVEL  
 EAN_CODE  
 PRODUCER_ID  
 IMAGE_ID  
   

Columns from PRODUCTS:

 PRODUCT  
 PRODUCT_ID  
 PRODUCT_SEARCH  

Columns in the join query that are uniquely identified by the column name may be specified by name alone. Columns that have the same name in the joined tables must be qualified by their respective table names.

The same query as above, but only three columns are returned:

 SELECT product, ean_code, price   FROM items       JOIN products ON items.product_id = products.product_id;  

Nesting Join Clauses

It is possible to nest join-clauses, for example:

List the category in addition to the EAN and price for any items released in December 1996:
 SELECT ean_code, price, category   FROM items       JOIN formats ON items.format_id = formats.format_id       JOIN categories ON categories.category_id = formats.category_id   WHERE release_date BETWEEN date'1996-12-01' AND date'1996-12-30';  
Result:

 EAN_CODE  
PRICE
 CATEGORY  
  9780006498957   
7.99 
 Books  
 724385487521  
9.98
 Music  
 731453076723  
29.98
 Music  
 53308951925  
11.98
 Music  

Complex Search Conditions and Joins

A join query can join any number of tables using complex search conditions to select the relevant information from each table.

List the product for any items with a release date in the future along with the item price in both Swedish and Danish crowns (SEK and DKK respectively):
 SELECT product,          CAST(price * exchange_rate AS DECIMAL(12,2)) AS cost,          currency   FROM items   JOIN products ON products.product_id = items.product_id   CROSS JOIN currencies   WHERE release_date > CURRENT_DATE     AND currencies.code IN ('SEK', 'DKK')   ORDER BY product, currency;  
Result:

 PRODUCT  
COST
 CURRENCY  
 Greatest Hits  
99.42
 Danish Kronor  
 Greatest Hits  
125.61
 Swedish Kronor  
 Pieces Of Fish  
113.64
 Danish Kronor  
 Pieces Of Fish  
143.58
 Swedish Kronor  
 The Future Foretold  
49.71
 Danish Kronor  
 The Future Foretold  
62.80
 Swedish Kronor  
 The Sql Quiz Book  
99.49
 Danish Kronor  
 The Sql Quiz Book  
125.70
 Swedish Kronor  

In formulating a search condition for a join query, it can help to write out the columns that would appear in a complete cross-product of the tables. The search condition is then formulated as though the query was a simple SELECT from the cross-product table.

Outer Joins

The joins in the previous sections were all inner joins. In an inner join between two tables, only rows that fulfill the join condition are present in the result.

An outer join, on the contrary, contains non-matching rows as well. The outer join has two options, LEFT and RIGHT.

Left Outer Join

Example
 SELECT ean_code, release_date, producer   FROM items   LEFT OUTER JOIN producers       ON items.producer_id = producers.producer_id   WHERE ean_code >= 800000000000   ORDER BY ean_code;  
Result:

EAN_CODE
 RELEASE_DATE  
 PRODUCER  
800488327626
 1998-08-11  
 Giants Of Jazz (Ita)  
801061007720
 2000-10-31  
 Warp Records  
4988002364947
 1999-09-28     
 -  
4988011353147
 1998-06-30  
 -  
5013145800423
 2000-03-14     
 Mint / Cherry Red  
5013929112322
 1999-10-12     
 Cherry Red  
5014438710221
 1994-12-27     
 Receiver Records  
5019317001728
 1994-12-15     
 Receiver Records  
7157761806273
 1996-01-18     
 Status Records  
...
 ...  
 ...  

In the example above all rows from the table to the left in the join clause, i.e. ITEMS, are present in the result; non-matching rows from the PRODUCERS table are filled with null values in the result.

Observe the difference in result for the next statement and the previous one.

 SELECT ean_code, release_date, producer   FROM items   LEFT OUTER JOIN producers       ON  items.producer_id = producers.producer_id       AND ean_code >= 800000000000   ORDER BY ean_code;  
Result:

EAN_CODE
 RELEASE_DATE  
 PRODUCER  
8811038120
 1991-08-27  
 -  
8811042127
 1991-10-22  
 -  
8811061326
 1992-05-19  
 -  
8811067021
 1992-12-22  
 -  
...
 ...  
 ...  
800488327626
 1998-08-11  
 Giants Of Jazz (Ita)  
801061007720
 2000-10-31  
 Warp Records  
4988002364947
 1999-09-28     
 -  
4988011353147
 1998-06-30  
 -  
5013145800423
 2000-03-14     
 Mint / Cherry Red  
...
 ...  
 ...  

The reason is that conditions in the where clause are applied to the result of the join-clause and not to the joined tables as is the case with the conditions in the on-clause.

Right Outer Join

A right outer join will take all records from the table to the right in the join-clause.

Nesting Outer Joins

As with inner joins, it is possible to nest join-clauses. Nested joins can be of different types, i.e. both inner and outer joins.

The result of nested outer joins can be somewhat unexpected though, as it is the result of the first join-clause that is the left table in the next join, not the right table in the first join-clause.

Example
 SELECT *   FROM tableA       LEFT JOIN tableB ON tableA.id = tableB.id       LEFT JOIN tableC ON tableA.id = tableC.id     

This query does first perform tableA LEFT JOIN tableB. The result is then used as left table when performing LEFT JOIN tableC.

To make this query clearer, parentheses can be added as:

 SELECT *   FROM (tableA LEFT JOIN tableB ON tableA.id = tableB.id)     LEFT JOIN tableC ON tableA.id = tableC.id  

Nested Selects

A form of SELECT, called a subselect, can be used in the search condition of a SELECT statement to form a nested query.

The main SELECT statement is then referred to as the outer select.

For example:

Select the products that have a release date in the future.
 SELECT product   FROM products   WHERE product_id IN (SELECT product_id                        FROM items                        WHERE release_date > CURRENT_DATE);  
Result:

 PRODUCT  
 Greatest Hits  
 Pieces Of Fish  
 The Future Foretold  
 The Sql Quiz Book  

To see how this works, evaluate the subselect first:

 SELECT product_id   FROM items   WHERE release_date > CURRENT_DATE;  
Result:

 PRODUCT_ID  
 30206  
 30618  
 31082  
 31083  

Then use the result of the subselect in the search condition of the outer select:

 SELECT product   FROM products   WHERE product_id IN (30206, 30618, 31082, 31083);  
Result:

 PRODUCT  
 Greatest Hits  
 Pieces Of Fish  
 The Future Foretold  
 The Sql Quiz Book  

Using Subselects

A subselect can be used in a search condition wherever the result of the subselect can provide the correct form of the data for the search condition.

Thus a subselect used with `=' must give a single value as a result.

A subselect used with IN, ALL or ANY must give a set of single values, see Retrieval with ALL, ANY, SOME.

A subselect used with EXISTS may give any result, see Retrieving Data Using EXISTS and NOT EXISTS.

Examples:
 WHERE column = (subselect)   WHERE column IN (subselect)   WHERE column = ALL (subselect)   WHERE column = ANY (subselect)   WHERE EXISTS (subselect)     

The UNION, EXCEPT and INTERSECT operators can be used to combine two or more subselects in more complex statements, see Union, Except and Intersect Queries.

Nested Queries

Many nested queries can equally well be written as simple joins. For example:

Select the products that have a release date in the future.
 SELECT product   FROM products   WHERE product_id IN (SELECT product_id                        FROM items                        WHERE release_date > CURRENT_DATE);     

or alternatively

 SELECT DISTINCT product   FROM products   JOIN items       ON products.product_id = items.product_id   WHERE items.release_date > CURRENT_DATE;     

Both these queries give exactly the same result. In most cases, the choice of which form to use is a matter of personal preference. Choose the form which you can understand most easily; the clearest formulation is least likely to cause problems.

Subselects in Queries

Queries may contain any number of subselects, for example:

List the producers (manufacturers) which have items that are more expensive than any of the items produced by Sony.
 SELECT producer   FROM producers   WHERE producer_id IN            (SELECT producer_id             FROM items             WHERE price >                    (SELECT MAX(price)                     FROM items                     WHERE producer_id =                             (SELECT producer_id                              FROM producers                              WHERE producer = 'SONY')));     

Note the balanced parentheses for the nested levels.

It is particularly important at this level of complication to think carefully through the query to make sure that it is correctly formulated.

Often, writing some of the levels as simple joins can simplify the structure. The previous example may also be written:

 SELECT DISTINCT producer   FROM producers   JOIN items        ON producers.producer_id = items.producer_id   WHERE price > (SELECT MAX(price)                  FROM items                  JOIN producers                      ON items.producer_id = producers.producer_id                  WHERE producer = 'SONY');  

Correlation Names

A correlation name is a temporary name given to a table to represent a logical copy of the table within a query.

There are three uses for correlation names:

  • simplifying complex queries
  • joining a table to itself
  • outer references in subselects

Simplifying Complex Queries Using Correlation Names

Using short correlation names into complicated queries can make the query easier to write and understand, particularly when qualified table names are used:

 SELECT mimer_store_music.artists.artist,          mimer_store.product_details.*   FROM mimer_store.product_details   JOIN mimer_store_music.titles       ON mimer_store.product_details.item_id =          mimer_store_music.titles.item_id   JOIN mimer_store_music.artists       ON mimer_store_music.artists.artist_id =          mimer_store_music.titles.artist_id   ORDER BY mimer_store_music.artists.artist;     

may be rewritten

 SELECT art.artist, pdt.*   FROM mimer_store.product_details AS pdt   JOIN mimer_store_music.titles AS ttl       ON pdt.item_id = ttl.item_id   JOIN mimer_store_music.artists AS art       ON art.artist_id = ttl.artist_id   ORDER BY art.artist;     

The keyword AS in the FROM clause may be omitted, but is recommended for clarity.

About Correlation Names

Correlation names are local to the query in which they are defined.

When a correlation name is introduced for a table name, all references to the table in the same query must use the correlation name.

The following expression is not accepted:

    ...      FROM mimer_store.product_details AS pdt,           mimer_store_music.titles AS ttl,      ...      WHERE ttl.item_id = mimer_store.product_details.item_id  

Joining a Table with Itself Using a Correlation Name

Joining a table with itself allows you to compare information in a table with other information in the same table. This can be done with a correlation name.

Select all currencies with the same exchange rate:
 SELECT c.currency, c.code, c.exchange_rate   FROM currencies AS c   JOIN currencies AS copy       ON  c.exchange_rate = copy.exchange_rate       AND c.currency <> copy.currency;  
Result:

 CURRENCY  
 CODE  
EXCHANGE_RATE
 Croatian Kuna  
 HRK  
7.0820
 Gourdes  
 HTQ  
7.0820
 Iraqi Dina  
 IQD  
1551.0000
 Uganda Shillings  
 UGX  
1551.0000

Here, the table CURRENCIES is joined to a logical copy of itself called COPY.

The first search condition finds pairs of currencies with the same exchange rate, and the second eliminates 'pairs' with the same currency name. Without the second condition in the search criteria, all currencies would be selected!

Without correlation names, this kind of query cannot be formulated. The following query would select all the currencies from the table:

 SELECT currency, code, exchange_rate   FROM currencies   WHERE currencies.exchange_rate = currencies.exchange_rate;  

Outer References in Subselects Using Correlation Names

In some constructions using subselects, a subselect at a lower level may refer to a value in a table addressed at a higher level. This kind of reference is called an outer reference.

 SELECT currency   FROM currencies   WHERE EXISTS (SELECT *                 FROM countries                 WHERE currency_code = currencies.code);     

This kind of query processes the subselect for every row in the outer select, and the outer reference represents the value in the current outer select row. In descriptive terms, the query says 'For each row in CURRENCIES, select the CURRENCY column if there are rows in COUNTRIEScontaining the current CODE value'.

If the qualifying name in an outer reference is not unambiguous in the context of the subselect, a correlation name must be defined in the outer select.

A correlation name may always be used for clarity, as in the following example:

 SELECT currency   FROM currencies AS c   WHERE EXISTS (SELECT *                FROM countries                WHERE currency_code = c.code);  

Retrieving Data Using EXISTS and NOT EXISTS

EXISTS is used to check for the existence of some row or rows which satisfy a specified condition. EXISTS differs from the other operators in that it does not compare specific values; instead, it tests whether a set of values is empty or not. The set of values is specified as a subselect.

The subselect following the EXISTS clause most often uses of `SELECT *' as opposed to `SELECT column-list' since EXISTS only searches to see if the set of values addressed by the subselect is empty or not - a specified column is seldom relevant in the subquery.

EXISTS (subselect) is true if the result set of the subselect is not empty

NOT EXISTS (subselect) is true if the result set of the subselect is empty

SELECT statements with EXISTS almost always include an outer reference linking the subselect to the outer select.

Examples of EXISTS

Find all currencies that are used in the COUNTRIES table:
 SELECT currency   FROM currencies AS c   WHERE EXISTS (SELECT *                 FROM countries                 WHERE currency_code = c.code);     

Without the outer reference, the select becomes a conditional `all-or-nothing' statement: perform the outer select if the subselect result is not empty, otherwise select nothing.

List all products where the producer (manufacturer) is not known:
 SELECT product   FROM products AS p   WHERE EXISTS (SELECT *                 FROM items                 WHERE producer_id IS NULL                   AND product_id = p.product_id);  

Examples of NOT EXISTS

The next example illustrates NOT EXISTS:

List all products where the producer (manufacturer) is not known:
 SELECT product   FROM products   WHERE NOT EXISTS (SELECT *                     FROM items                     JOIN producers ON items.producer_id = producers.producer_id                     WHERE product_id = products.product_id);  
Result:

 PRODUCT  
 Invictus  
 Middle Of Nowhere  

Negated EXISTS

Negated EXISTS clauses must be handled with care. There are two semantic `opposites' to EXISTS, with very different meanings:

 WHERE EXISTS (SELECT *                 FROM artists                 WHERE artist = 'Enigma')     

is true if at least one artist is called Enigma.

 WHERE NOT EXISTS (SELECT *                     FROM artists                     WHERE artist = 'Enigma')  

is true if no artist is called Enigma.

But

 WHERE EXISTS (SELECT *                 FROM artists                 WHERE artist <> 'Enigma')     

is true if at least one artist is not called Enigma.

 WHERE NOT EXISTS (SELECT *                     FROM artists                     WHERE artist <> 'Enigma')     

is true if no artist is not called Enigma, that is if every artist is called Enigma.

Retrieval with ALL, ANY, SOME

Subselects that return a set of values may be used in the quantified predicates ALL, ANY or SOME. Thus

 WHERE PRICE < ALL (subselect)     

selects rows where the price is less than every value returned by the subselect

 WHERE PRICE < ANY (subselect)     

selects rows where the price is less than at least one of the values returned by the subselect

Select countries that have an exchange rate of less than one:
 SELECT country   FROM countries   WHERE currency_code <> ALL (SELECT code                               FROM currencies                               WHERE exchange_rate >= 1.0);     

If the result of the subselect is an empty set, ALL evaluates to true, while ANY or SOME evaluates to false.

An alternative to using ALL, ANY or SOME in a value comparison against a general sub-select, is to use EXISTS or NOT EXISTS to see if values are returned by a sub-select which only selects for specific values. For example:

Select countries where the associated currency code contains the letter 'E' as the middle character in the code:
 SELECT country   FROM countries   WHERE currency_code = ANY (SELECT code                              FROM currencies                              WHERE code LIKE '_E_');

is equivalent to:

 SELECT country   FROM countries AS c   WHERE EXISTS (SELECT *                 FROM currencies                 WHERE code LIKE '_E_'                   AND code = c.currency_code);  

Union, Except and Intersect Queries

The UNION, EXCEPT and INTERSECT operators combine the results of two select clauses.

UNION first merges the result tables specified by the separate selects and then eliminates duplicate rows from the merged set. (UNION ALL does not eliminate duplicate rows.)

EXCEPT takes the distinct rows from the first select and returns the rows that do not appear in the second select. (EXCEPT ALL does not eliminate duplicate rows.)

INTERSECT takes the results of two selects and returns only rows that appear in both selects, after removing duplicate rows from the final result set. (INTERSECT ALL does not eliminate duplicate rows.)

Columns which are merged by UNION, EXCEPT and INTERSECT must have compatible data types (numerical with numerical, character with character, etc).

Subselects addressing more than one result column are merged column by column in the order of selection. The number of columns addressed in each subselect must be the same.

The column names in the result of a UNION, EXCEPT or INTERSECT are taken from the names in the first subselect. Use labels in the first subselect to assign different column names to the result table.

In UNION, EXCEPT and INTERSECT queries, you may need to add an empty column so that columns not represented in both queries in the statement are retained in the result set. This is done by casting a null value to a matching datatype.

Example
 SELECT ean_code, release_date, producer   FROM items   INNER JOIN producers       ON items.producer_id = producers.producer_id   UNION ALL   SELECT ean_code, release_date, CAST(NULL AS char)   FROM items   WHERE NOT EXISTS       (SELECT * FROM producers        WHERE items.producer_id = producers.producer_id)  

UNION Examples

Select the different codes for currencies and countries that start with the letter 'D':
 SELECT code   FROM currencies   WHERE code LIKE 'D%'   UNION   SELECT currency_code   FROM countries   WHERE country LIKE 'D%';     

The result is obtained by merging the results of the two selects and eliminating duplicates:

 SELECT code                   SELECT currency_code   FROM currencies               FROM currencies   WHERE code LIKE 'D%;          WHERE country LIKE 'D%';     

CODE

CURRENCY_CODE
DJF

DJF
DKK

DKK
DOP

XCD
DZD

DOP

and the UNION gives the result table:

 CODE  
 DJF  
 DKK  
 DOP  
 DZD  
 XCD  

To retain duplicates in the result table, use UNION ALL in place of UNION, see the Mimer SQL Reference Manual, UNION or UNION ALL, for details.

Merge the codes and names of currencies where the code begins with 'D' with the codes and names of the countries where the country begins with 'D':
 SELECT code, currency AS currency_or_country   FROM currencies   WHERE code LIKE 'D%'   UNION   SELECT currency_code, country   FROM countries   WHERE country LIKE 'D%'   ORDER BY code;  
Result:

CODE
 CURRENCY_OR_COUNTRY  
DJF
 Djibouti  
DJF
 Djibouti Francs  
DKK
 Danish Kronor  
DKK
 Denmark  
DOP
 Dominican Pesos  
DOP
 Dominican Republic  
DZD
 Algerian Dinars  
XCD
 Dominica  
Find the lowest and highest exchange_rates:

Unions can be used to combine information from the same table.

 SELECT 'Highest', MAX(exchange_rate) AS rate   FROM currencies   UNION ALL   SELECT 'Lowest', MIN(exchange_rate)   FROM currencies   ORDER BY rate;  
Result:

   
 RATE  
 Lowest  
 0.2644  
 Highest  
 1035000.0000  

EXCEPT Examples

Select the codes from currencies, except those that also are found in countries, starting with the letter 'D':
 SELECT code   FROM currencies   WHERE code LIKE 'D%'   EXCEPT   SELECT currency_code   FROM countries   WHERE country LIKE 'D%';     

The result is obtained by taking the first result and then remove the rows also found in the second select, and finally eliminating duplicates:

 SELECT code                   SELECT currency_code   FROM currencies               FROM currencies   WHERE code LIKE 'D%;          WHERE country LIKE 'D%';     

CODE

CURRENCY_CODE
DJF

DJF
DKK

DKK
DOP

XCD
DZD

DOP

and the EXCEPT gives the result table:

 CODE  
 DZD  

To retain duplicates in the result table, use EXCEPT ALL in place of EXCEPT, see the Mimer SQL Reference Manual, EXCEPT or EXCEPT ALL, for details.

INTERSECT Examples

Select the codes from currencies and countries that exist in both tables, starting with the letter 'D':
 SELECT code   FROM currencies   WHERE code LIKE 'D%'   INTERSECT   SELECT currency_code   FROM countries   WHERE country LIKE 'D%';     

The result is obtained by taking the first result and then remove the rows also found in the second select, and finally eliminating duplicates:

 SELECT code                   SELECT currency_code   FROM currencies               FROM currencies   WHERE code LIKE 'D%;          WHERE country LIKE 'D%';     

CODE

CURRENCY_CODE
DJF

DJF
DKK

DKK
DOP

XCD
DZD

DOP

and the INTERSECT gives the result table:

 CODE  
 DFJ  
 DKK  
 DOP  

To retain duplicates in the result table, use INTERSECT ALL in place of INTERSECT, see the Mimer SQL Reference Manual, INTERSECT or INTERSECT ALL, for details.

 

http://developer.mimer.se/documentation/html_110/Mimer_SQL_Engine_DocSet/Retreiving_Data17.html

 


 


 

 

 

 

©2024 tigerquesttech.com