select * from endPoint e inner join Gadgets g where g.GadgetID = e.endPointID and g.endPointType = 'Gadget'>might fail if the domains of GidgetID, GizmoID, and GadgetID are not identical. New features might alleviate the technical problems, but it is still an incorrect design that is tied to implementation and not to set theory.
with wireBindings (wireID,Direction, gadgetID) as ( select wireID,Direction,endPointID from wired where endPointType = 'Gadget' ) select * from wireBindings w inner join gadgets g on w.gadgetID = g.gadgetIDIf gadgetID,gidgetID,gizmoID domains are not of the same type than I'd question the design.